In PostgreSQL 9.6, what's the simplest way to expand a JSONB column filled with simple JSON dicts?

json, jsonb, postgresql, sql

Solution

Use the `normal_table` type as the base type to the `jsonb_populate_record` function:

create table normal_table (
    id int,
    first_field int,
    second_field int
);

with json_table (json_field) as ( values
    ('{"first_field": 2 , "second_field": 42}'::jsonb)
)
select (jsonb_populate_record(null::normal_table, json_field)).*
from json_table
;
 id | first_field | second_field                                                                                                                                          
----+-------------+--------------                                                                                                                                         
    |           2 |           42

If it is necessary to generate the `id` to be inserted use `jsonb_set`:

with json_table (json_field) as ( values
    ('{"first_field": 2 , "second_field": 42}'::jsonb),
    ('{"first_field": 5 , "second_field": 1}')
)
select (
    jsonb_populate_record(
        null::normal_table,
        jsonb_set(json_field, '{id}', (row_number() over())::text::jsonb)
    )
).*
from json_table
;
 id | first_field | second_field 
----+-------------+--------------
  1 |           2 |           42
  2 |           5 |            1

Problem

Say, I have a table `json_table` with a JSONB column, `json_field`. Each element in this column is a single uncomplicated dict, e.g., ``` {'first_field': 2 , 'second_field': 42} ``` Is there a way to create a new table were the dicts are turned into columns? My current approach is as follows: ``` CREATE TABLE normal_table ... first_field, second_field ... etc; INSERT INTO normal_table ( id, first_field, second_field, ... ) SELECT id, json_field->>'first_field', json_field->>'second_field', ... FROM json_table; ``` Is there a way to do something like the following? ``` SELECT id, expand_json_dict(json_field) FROM json_table; ``` Or a similar succinct way of doing it? The JSONB column has a lot of fields I want to expand, and the queries become unwieldy. I've actually made a Python function which generates create/insert scripts. Still, I'd love for there to be a nice PostgreSQL way to do it. Any thoughts? Edit The following is the working solution based on feedback here. Thanks guys. ``` drop table if exists json_table; create table json_table ( id int, json_field jsonb ); insert into json_table VALUES (2, ('{"second_field": 43, "first_field": 3}'::jsonb)), (1, ('{"first_field": 2 , "second_field": 42}'::jsonb)); drop table if exists normal_table; create table normal_table ( id int, first_field int, second_field int ); insert into normal_table select ( jsonb_populate_record( null::normal_table, jsonb_set(json_field, '{id}', id::text::jsonb) ) ).* from json_table; select * from normal_table; ```

Original source