Sql to filter data from a json type column-CodePudding

I have a table A and in it I have a column of type json that receives an array of dates. I need to fetch all female records from table A. I need to return a specific date from each record in table A.

----------------------------------------------------------------------------------- 
table A
-----------------------------------------------------------------------------------
gender (string)      date (json)
-----------------------------------------------------------------------------------
feminine             {"2022-01-01": "A1", "2022-01-02": "A2", "2022-01-03": "A3" } 
masculine            {"2022-01-01": "B1", "2022-01-02": "B2", "2022-01-03": "B3" } 
feminine             {"2022-01-01": "C1", "2022-01-02": "C2", "2022-01-03": "C3" } 
masculine            {"2022-01-01": "D1", "2022-01-02": "D2", "2022-01-03": "D3" } 
-----------------------------------------------------------------------------------

I need a query that returns me:

feminine   "2022-01-01": "A1"
feminine   "2022-01-01": "C1"

only female lines and only date 2022-01-01

CodePudding user response：

SELECT gender, '"2022-01-01": ' || date->'2022-01-01'
  FROM A
 WHERE gender = 'feminine'

see dbfiddle

CodePudding user response：

One option is to use json_each() function such as

SELECT gender, CONCAT('"',key,'" : ',value) AS date
  FROM A
 CROSS JOIN LATERAL json_each(date) AS j
 WHERE gender = 'feminine'
   AND key = '2022-01-01'

Demo