PostgreSQL – Converting Undefined JSONB Fields into a Row Dynamically

jsonpostgresqlselect

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?

Best Answer

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.

Related Question