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

Postgresql, retrieve resultset for specific key from json array

I have Postgres column of JSONB array of objects, looking like this :

[{"key": "meetingDate", "value": "2022-08-22"}, {"key": "userName", "value": "Testing User"}]

how can i get the result like this

meetingDate userName
2022-08-22 TestingUser
Tags:

2

Answers


  1. 0

    Use json_array_elements (db fiddle here).

    with t(v) as (values
  ('[{"key": "meetingDate", "value": "2022-08-22"}, {"key": "userName", "value": "Testing User"}]'::jsonb)
)
select (select a.e->>'value' from json_array_elements(t.v::json) a(e) where a.e->>'key' = 'meetingDate') as meetingDate
     , (select a.e->>'value' from json_array_elements(t.v::json) a(e) where a.e->>'key' = 'userName') as userName
from t
    Login or Signup to reply.
  2. 0

    You can use a JSON path expression:

    select jsonb_path_query_first(the_column, '$[*] ? (@.key == "meetingDate").value') #>> '{}' as meetingdate,
       jsonb_path_query_first(the_column, '$[*] ? (@.key == "userName").value') #>> '{}' as username
from the_table;

    jsonb_path_query_first returns a jsonb value, but there is no direct cast from there to text. The #>> '{}' is a small hack to convert the jsonb value to a text value. If you are OK with a jsonb value, you can remove it.

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

Back To Top
Search