Question 278144 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to query JSON Column in postgres

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search