Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →

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.

share|improve this question
    
    
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 Aug 18 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 Aug 18 at 15:34
up vote 1 down vote accepted
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.

share|improve this answer
    
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 Aug 18 at 15:32
    
Can it be achieved without a WITH statement? – Tom G Aug 18 at 15:39
    
q is the table you were referring to, you used that as a proxy for the original table. – Patrick Aug 18 at 16:00
    
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] – Tom G Aug 18 at 16:32
1  
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. – Patrick Aug 18 at 17:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.