CODEWITHSUNDEEP
sqljsonpostgresqljsonb
Neil Middleton
Neil Middleton

Reputation: 22238

Extracting JSON data into simple columns

I have inherited a table that contains a column which looks something like this:

field_a::json
=============
{'a':['1', '2']}
{'b':['foo', 'bar']}
{'a':[null, '3']}

Essentially, I want to convert this into something more usable in a view or similar. There is only ever one key, and the data is always a two element array. This is what I'm aiming for:

field|value1|value2
===================
a    |     1|     2
b    |   foo|   bar
c    |  null|     3

How might I query this given I don't know the name of any of the keys in play here? This is on PG11

Upvotes: 1

Views: 82

Answers (2)

klin
klin

Reputation: 121604

You can create a view that normalizes the data:

create view view_of_my_table as
select id, key::text, value->>0 as value1, value->>1 as value2
from my_table
cross join jsonb_each(field_a)

select *
from view_of_my_table

 id | key | value1 | value2 
----+-----+--------+--------
  1 | a   | 1      | 2
  2 | b   | foo    | bar
  3 | c   |        | 3
(3 rows)

Note that every select query on the view implies querying the source table with the function jsonb_each(), so this is not the most performant way. To improve performance, you can use a materialized view and refresh it after any insert/update of the source table.

Live demo in db<>fiddle.

Upvotes: 1

dwelle
dwelle

Reputation: 7284

Probably horribly inefficient, but it does the job:

DROP TABLE IF EXISTS source_table;
CREATE TEMP TABLE source_table ( field_a JSONB );

DROP TABLE IF EXISTS target_table;
CREATE TEMP TABLE target_table ( field TEXT, value1 JSONB, value2 JSONB );

INSERT INTO source_table VALUES
    ('{"a":["1", "2"]}'),   
    ('{"b":["foo", "bar"]}'),
    ('{"a":[null, 3]}');

INSERT INTO target_table
SELECT
    tmp.obj->>'key',
    COALESCE( TO_JSONB(((tmp.obj->'value')::JSONB)->0), 'null' ),
    COALESCE( TO_JSONB(((tmp.obj->'value')::JSONB)->1), 'null' )
FROM (
    SELECT TO_JSON(x) obj FROM (SELECT JSONB_EACH(field_a) x FROM source_table) AS x
) AS tmp;

SELECT * FROM target_table;

yields:

field|value1|value2
===================
"a" |   "1"|   "2"|
"b" | "foo"| "bar"|
"a" |  null|     3|

Where value1 and value2 are JSONB columns, and null is retained as JSONB.

Also, even though you specified that each object contains just a single key, the above solution will work regardless of how many keys it contains.

PosgreSQL 9.5+

Upvotes: 0

Related Questions