postgres extract JSON column key and values to separate columns

Question

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

GMB · Accepted Answer · 2020-02-19 17:45:49Z

1

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

edited Feb 19, 2020 at 17:45

answered Feb 19, 2020 at 17:36

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

ziggy Over a year ago

that was nifty, thanks. I also just found out about the json_each function

GMB Over a year ago

@ziggy: ah yes, json_each() seems better - I added it to my answer.

Collectives™ on Stack Overflow

postgres extract JSON column key and values to separate columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related