CODEWITHSUNDEEP
sqljsonpostgresqljsonb
André C. Andersen
André C. Andersen

Reputation: 9375

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

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;

Upvotes: 4

Views: 2186

Answers (2)

user330315
user330315

Reputation:

You can create a type (record) that reflects your keys and then use json_populate_record:

create type my_type as (first_field varchar, second_field varchar);

SELECT id, (json_populate_record(null::my_type, json_field)).*
FROM json_table;

If there are keys in the JSON document that are not present in the type, they are simply ignored. If there are fields in the type definition that don't have a match in the JSON document they will be null.

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

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

Upvotes: 4

Related Questions