Extract key, value from json objects in Postgres
Solution 1
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.
The table q
can also be a very complicated sub-query (or a VALUES
clause, like in your question). In the function, the appropriate column is used from the result of evaluating that sub-query, so you use only a reference to the alias of the sub-query and the (alias of the) column in the sub-query.
Solution 2
This will solve it as well:
select you_table.id , js.key, js.value
from you_table, json_each(you_table.data) as js
Solution 3
Another way that i think is very easy to work when you have multiple jsons to join is doing something like:
SELECT data -> 'key' AS key,
data -> 'value' AS value
FROM (SELECT Hstore(Json_each_text(data)) AS data
FROM "your_table") t;
Tom G
Updated on July 09, 2022Comments
-
Tom G almost 2 years
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.
-
Tom G over 7 yearsThanks Patrick. I'm still a little confused about how I fit this around my original query. Do I define 'q' using a 'WITH' statement?
-
Tom G over 7 yearsCan it be achieved without a WITH statement?
-
Patrick over 7 years
q
is the table you were referring to, you used that as a proxy for the original table. -
Tom G over 7 yearsAh ok. So in my real example, there isn't actually a table, but an inner query called q. I guess in that case, a WITH statement is the only way? [p.s. I've accepted your answer]
-
Patrick over 7 yearsYou can use either, but there is a subtle difference between a WITH statement and a sub-query: the first gets processed as specified and then used in the main query, while a sub-query will be "flattened" and then merged into the main query before processing. This can have important performance implications; see this excellent blog post by Craig Ringer for a detailed explanation.
-
Tom G over 7 yearsYeah, that's why I was keen to do it "without" a WITH statement. To do it with a sub-query, do I need to repeat the subquery twice (i.e. in both places where you have 'q' in your query above)? Or is there a better way?
-
Rob212 over 6 yearsThanks for the good answer Patrick, this has just proven very helpful to me. Much appreciated.
-
Dmitri over 6 yearsThank you! I've spent hours trying to do exactly this, and an "ON true" join would never have occurred to me.
-
Stanislav Savulchik over 6 yearsIf I got it right from the docs postgresql.org/docs/9.6/static/… there is a way to describe the lateral join of a table with a set-returning function just using
SELECT * FROM q, LATERAL jsonb_each(q.data)
without confusingJOIN ... ON TRUE
. It produces the expected result of joining each row fromq
with the result of set-returning function for that exact row.