How to convert postgresql 9.4 jsonb to object without function/server side language
jsonb, postgresql
Solution
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:
- Query combinations with nested array of records in JSON datatype
Problem
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]} ```