How to query JSON Column in postgres-CodePudding

My table name is participant_preference and the column name is 'value' and the JSON is in the column as below,

{
  "DiaryUsers": [
    {
      "UserId": "HUGWFJJ6",
      "IsDiaryVisible": false,
      "ParticipantName": "Dr. Mckenzie, Alistair G [SP000024]",
      "Status": "A"
    },
    {
      "UserId": "HUV65PV1",
      "IsDiaryVisible": true,
      "ParticipantName": "Dr. Mckenzie, Alistair G [SP000024]",
      "Status": "A"
    }
  ]
}

I need help to query UserId for specific id i.e UserId=HUV65PV1 and expecting to get only this users data from sql query.

My database is postgres sql.

Please let me know if I missed any details.

CodePudding user response：

Use jsonb_array_elements in a CROSS JOIN to access the array elements, e.g.

SELECT e.* FROM t
CROSS JOIN LATERAL jsonb_array_elements(col->'DiaryUsers') e
WHERE e->>'UserId' = 'HUV65PV1';

Demo: db<>fiddle

CodePudding user response：

Here is a way to do it using jsonb_path_query_array :

select jsonb_path_query_array(col->'DiaryUsers', '$[*] ? (@.UserId == "HUGWFJJ6")') as user
from participant_preference 
where col->'DiaryUsers' @> '[{"UserId":"HUGWFJJ6"}]';

Demo here