Extract key, value from json objects in Postgres

Question

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.

Possible duplicate of How to convert postgresql 9.4 jsonb to object without function/server side language — e4c5
– e4c5, Commented Aug 18, 2016 at 6:34
Use functions json_object_keys or json_each as table, not as column: select id, j.key, j.value from my_table, json_each(data) j — Abelisto
– Abelisto, Commented Aug 18, 2016 at 8:27
Thanks Abelisto. Surely I would end up with a full cartesian product, rather than the table I'm looking for, using the query you suggest? — Tom G
– Tom G, Commented Aug 18, 2016 at 15:34

Patrick · Accepted Answer · 2016-08-19 02:46:03Z

56

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.

edited Aug 19, 2016 at 2:46

answered Aug 18, 2016 at 12:23

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

9 Comments

Tom G Over a year ago

Thanks 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 a year ago

Can it be achieved without a WITH statement?

Patrick Over a year ago

q is the table you were referring to, you used that as a proxy for the original table.

Tom G Over a year ago

Ah 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 a year ago

You 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.

|

Roman M · Accepted Answer · 2020-06-03 16:27:53Z

8

This will solve it as well:

select you_table.id , js.key, js.value
from you_table, json_each(you_table.data) as js

answered Jun 3, 2020 at 16:27

Roman M

13.2k2 gold badges21 silver badges20 bronze badges

Comments

Gregorio Freidin · Accepted Answer · 2020-05-28 02:08:26Z

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;

edited May 28, 2020 at 2:08

answered May 26, 2020 at 17:49

Gregorio Freidin

1111 silver badge3 bronze badges

1 Comment

kbluue Over a year ago

How do you define the path. Example: if the jsonblob is an array of objects, and you want the a set containing a field from each object in the array.

SternK · Accepted Answer · 2022-12-04 14:32:06Z

0

you can

select js.key , js.value 
from metadata, json_each(metadata.column_metadata) as js 
where id='6eec';

edited Dec 4, 2022 at 14:32

SternK

13.3k34 gold badges40 silver badges55 bronze badges

answered Nov 30, 2022 at 9:46

Kirty Dash

11 bronze badge

1 Comment

Jeremy Caney Over a year ago

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, can you edit your answer to include an explanation of what you're doing and why you believe it is the best approach?

Collectives™ on Stack Overflow

Extract key, value from json objects in Postgres

4 Answers 4

9 Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related