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.