I have a table with a field of type jsonb
, I would like to split the column of field jsonb for all the json keys. This column lacks a schema. For instance:
From
CREATE TABLE v(id,jsonb)
AS VALUES
(1,'{"a":"4", "b":"5"}'::jsonb),
(2,'{}'),
(3,'{"a":"8", "c":"9", "d":"9"}');
to
id | a | b | c | d
1 | 4 | 5 | |
3 | 8 | | 9 | 9
for this specific case one solution is
select * from table, json_to_record(optional) as x("a" text, "b" text, "c" text, d text)
As you can see the keys can vary and and in a big database is difficult put all the keys in my real problem I have 31 keys and on other hand If I want to reuse this script in other table I have to fill manually the keys.
Is there any way to do a select all over the keys of jsonb without specify manually the keys?