Tom G Tom G - 3 months ago 30
JSON Question

Extract key, value from json objects in Postgres

I have a Postgres table that has content similar to this:

id | data

1 | {"a":"4", "b":"5"}
2 | {"a":"6", "b":"7"}
3 | {"a":"8", "b":"9"}


The first column is an integer and the second is a json column.

I want to be able to expand out the keys and values from the json so the result looks like this:

id | key | value

1 | a | 4
1 | b | 5
2 | a | 6
2 | b | 7
3 | a | 8
3 | b | 9


Can this be achieved in Postgres SQL?




What I've tried

Given that the original table can be simulated as such:

select *
from
(
values
(1, '{"a":"4", "b":"5"}'::json),
(2, '{"a":"6", "b":"7"}'::json),
(3, '{"a":"8", "b":"9"}'::json)
) as q (id, data)


I can get just the keys using:

select id, json_object_keys(data::json)
from
(
values
(1, '{"a":"4", "b":"5"}'::json),
(2, '{"a":"6", "b":"7"}'::json),
(3, '{"a":"8", "b":"9"}'::json)
) as q (id, data)


And I can get them as record sets like this:

select id, json_each(data::json)
from
(
values
(1, '{"a":"4", "b":"5"}'::json),
(2, '{"a":"6", "b":"7"}'::json),
(3, '{"a":"8", "b":"9"}'::json)
) as q (id, data)


But I can't work out how to achieve the result with id, key and value.

Any ideas?

Note: the real json I'm working with is significantly more nested than this, but I think this example represents my underlying problem well.

Answer
SELECT q.id, d.key, d.value
FROM q
JOIN json_each_text(q.data) d ON true
ORDER BY 1, 2;

The function json_each_text() is a set returning function so you should use it as a row source. The output of the function is here joined laterally to the table q, meaning that for each row in the table, each (key, value) pair from the data column is joined only to that row so the relationship between the original row and the rows formed from the json object is maintained.

Comments