PostgreSQL converting undefined JSONB fields into a row dynamically

Question

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?

Answer 1

My question : Is there any way to do a select all over the keys of jsonb without specify manually the keys?

No, because there is no way to have a query return an undefined result set. However, if the table is not accepting new queries you can generate a dynamic sql statement.

SELECT FORMAT(
 $$ SELECT * FROM %I.%I CROSS JOIN LATERAL jsonb_to_record(%I) AS rs(%s); $,ドル
 'public',
 'v',
 'jsonb',
 array_to_string(
 (SELECT ARRAY(SELECT DISTINCT col FROM v CROSS JOIN LATERAL jsonb_object_keys(jsonb) AS t(col) ORDER BY col)), ' text , '
 ) || ' text'
);

Then run that query, or run \gexec in psql.

 format 
--------------------------------------------------------------------------------------------------------------
 SELECT * FROM public.v CROSS JOIN LATERAL jsonb_to_record(jsonb) AS rs(a text , b text , c text , d text); 
(1 row)
test=# \gexec
 id | jsonb | a | b | c | d 
----+--------------------------------+---+---+---+---
 1 | {"a": "4", "b": "5"} | 4 | 5 | | 
 2 | {} | | | | 
 3 | {"a": "8", "c": "9", "d": "9"} | 8 | | 9 | 9
(3 rows)

You may or may not want to build some type inference with jsonb_typeof back to pg types: keep in mind, you can never go back to integer or something, but you should be able to store the numbers as double precision.