PostgresQL - Converting JSON values based on keys, into columns

user_11285492 0
Viewed 161

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?

select postgresql sql json
2 Answers

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.

user_3303150

You can use a subquery when selecting the all the key values for each row in table:

select t.id, 
       (select v.value -> 'value' from jsonb_array_elements(t.js) v where (v.value -> 'key')::text = '"abc"') abc,
       (select v.value -> 'value' from jsonb_array_elements(t.js) v where (v.value -> 'key')::text = '"def"') def,
       (select v.value -> 'value' from jsonb_array_elements(t.js) v where (v.value -> 'key')::text = '"fgh"') fgh
from test t;

See demo here.

user_73267380
Related