PostgresQL - Converting JSON values based on keys, into columns

Question

Using postgresql,

This is the table I have:

id | json
1  | [{"key":"abc", "value":"123"}, {"key":"def", "value":"456"}] 
2  | [{"key":"def", "value":"789"}, {"key":"fgh", "value":"456"}]

I'd like to turn it into this

id | abc  | def | fgh
1  | 123  | 456 | NULL
2  | NULL | 789 | 456

I think the answer lies in between using json_array_elements, array_agg and some form of nested subquery.

I'm a little confused as to how to apply them in this scenario.

What query can I write for this?

Answer 1

You need to use jsonb_array_elements() and then use a CASE expression to extract the value if the key is present.

select t.id, 
       case when x.element ->> 'key' = 'abc' then x.element ->> 'value' end as abc,
       case when x.element ->> 'key' = 'def' then x.element ->> 'value' end as def,
       case when x.element ->> 'key' = 'fgh' then x.element ->> 'value' end as fgh
from the_table t
  cross join jsonb_array_elements(the_column) as x(element);

If you aren't using jsonb (which you should), use json_array_elements() instead.

---

Before you ask: it's not possible to have the column list dynamic. The number, names and types of the columns of a SQL query must be known to the database when it parses the statement. They can't be evaluated when the query is executed.