CODEWITHSUNDEEP
sqljsonpostgresqlaggregate-functions
ziggy
ziggy

Reputation: 1538

postgres extract JSON column key and values to separate columns

I have a table that has a column that was created by 

json_object_agg(eventid,date_now_range) eventids_range

this what the data looks like stored in the table

{ "527813" : 184, "525947" : 133, "529957" : 178, "531490" : 143 }

I want to select this record and have the key and value be its own record

so this 

select eventids_range from special_event_conflicts

would return something like this

col a     col b
527813    184
525947    133
529957    178
531490    143

Upvotes: 1

Views: 1734

Answers (1)

GMB
GMB

Reputation: 222432

Here is one option:

select k.cola, v.colb
from mytable t
cross join lateral json_object_keys(t.eventids_range) as k(cola)
cross join lateral json_extract_path_text(t.eventids_range, k.cola) v(colb)

This works by first extracting the keys from the object using json_object_keys(), and then accessing the corresponding values with json_extract_path_text().

Demo on DB Fiddle:

cola   | colb
:----- | :---
527813 | 184 
525947 | 133 
529957 | 178 
531490 | 143

Another - probably better - solution is to use json_each():

select x.cola, x.colb
from mytable t
cross join lateral json_each(t.eventids_range) as x(cola, colb)

Demo on DB Fiddle

Upvotes: 1

Related Questions