Postgresql, retrieve resultset for specific key from json array

Question

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

Does you array always contain only two elements? What if there are 5 usernames but only 3 meeting dates? — user330315
– user330315, Commented Sep 30, 2022 at 9:47
the array only contains this structure one meeting date and one username — Hemant Kushwaha
– Hemant Kushwaha, Commented Sep 30, 2022 at 9:56
Would be a lot easier if it was {"meetingDate": "2022-08-22", "userName": "TestingUser"} — user330315
– user330315, Commented Sep 30, 2022 at 10:01
but i cant change the structure as the structure i mentioned is required for the business logic — Hemant Kushwaha
– Hemant Kushwaha, Commented Sep 30, 2022 at 10:08

Tomáš Záluský · Accepted Answer · 2022-09-30 10:48:35Z

3

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

answered Sep 30, 2022 at 10:48

Tomáš Záluský

12.3k4 gold badges44 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2022-09-30 10:55:28Z

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.

answered Sep 30, 2022 at 10:55

user330315

Collectives™ on Stack Overflow

Postgresql, retrieve resultset for specific key from json array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related