5

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?

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.