How to convert postgresql 9.4 jsonb to object without function/server side language

11
votes

Is it possible to transform postgresql 9.4 jsonb data without creating function and without using any server side programming language?

CREATE TABLE test (id SERIAL PRIMARY KEY,data JSONB);
INSERT INTO test(data) VALUES('{"a":1,"b":2}');
INSERT INTO test(data) VALUES('{"a":3,"b":4,"c":7}');
INSERT INTO test(data) VALUES('{"a":5,"b":5,"d":8}');
SELECT * FROM test;
 id |           data       
----+-------------------------
  1 | {"a": 1, "b": 2}
  2 | {"a": 3, "b": 4, "c": 7}
  3 | {"a": 5, "b": 5, "d": 8}

to transform it into:

 {1:[1,2,null,null],2:[3,4,7,null],3:[5,5,null,8]}
1
postgresqljsonb

1 Answers

15
votes

Use jsonb_populate_record() (or json_populate_record() for json) with a well known row type as target. You can use a temp table to register a type for ad-hoc use (if you can't use an existing table or custom composite type):

CREATE TEMP TABLE obj(a int, b int, c int, d int);

Then:

SELECT t.id, d.*
FROM   test t
     , jsonb_populate_record(null::obj, t.data) d;

Or use jsonb_to_record() (or json_to_record() for json) and provide a column definition list with the call:

SELECT t.id, d.*
FROM   test t
     , jsonb_to_record(t.data) d(a int, b int, c int, d int);

Or extract and cast each field individually:

SELECT id, (data->>'a')::int AS a, (data->>'b')::int AS b
         , (data->>'c')::int AS c, (data->>'d')::int AS d
FROM   test;

All three work for json and jsonb alike. Just use the respective function variant.

Related: