Extract and expand Postgres JSON column data into separate table

Question

There is a Postgres 9.6 table with these columns:

targettable
------------------------------------------------------------------

id | name | jsonbdata                                      | class
------------------------------------------------------------------
1  | A    | {"a":"1","b":"2","c":[{"aa":"1"},{"bb":"2"}]}  | 1
2  | B    | {"a":"2","b":NULL,"c":[{"aa":"3"},{"bb":"2"}]} | 1
3  | C    | {"z":"1","y":"2"}                              | 2

jsonbdata holds JSON objects with different structures, but share identical structure within the same class.

Question: I'd like to extract all jsonbdata rows matching a class into an empty new temp table with columns for each top level JSON key, and need some help structuring my query.

Where I'm at now:

create temp table testtable (id serial primary key);

with testrow as (select * from targettable where class = 1 limit 1)
select * from jsonb_populate_record(null::testtable, (select to_jsonb(jsonbdata) from testrow));

I think this might work if testtable had column names matching the JSON keys, but I'm unsure how to add table columns based on keys from a JSON object.

klin · Accepted Answer · 2017-08-13 15:25:13Z

3

You can use the function create_jsonb_flat_view() described in this answer.

Create a table (or a temporary table or a view) for a given class:

create table targettable_class_1 as
-- create temp table targettable_class_1 as
-- create view targettable_class_1 as
select *
from targettable
where class = 1;

and use the function to create a flat view:

select create_jsonb_flat_view('targettable_class_1', 'id, name', 'jsonbdata');

select *
from targettable_class_1_view;

 id | name | a | b |             c              
----+------+---+---+----------------------------
  1 | A    | 1 | 2 | [{"aa": "1"}, {"bb": "2"}]
  2 | B    | 2 |   | [{"aa": "3"}, {"bb": "2"}]
(2 rows)

answered Aug 13, 2017 at 15:25

klin

123k15 gold badges240 silver badges262 bronze badges

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

4 Comments

sheepgobeep Over a year ago

This looks promising. I'll parse the function in that answer and report back. Thanks!

sheepgobeep Over a year ago

Function works as expected! The function seems to throw an error if a passed in key has a single quote though. Contrived example: {"Fruit's name":"Mango"} generates a syntax error.

klin Over a year ago

@sheepgobeep - I've corrected the function, you can check it. Thanks for the tip.

sheepgobeep Over a year ago

Aces! Much more elegant than the convoluted path I was going down.

Collectives™ on Stack Overflow

Extract and expand Postgres JSON column data into separate table

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related