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

CodePudding user response：

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

CodePudding user response：

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.