In PostgreSQL 9.6, what's the simplest way to extend a JSONB column filled with simple JSON voice recorders?
Let's say I have a table json_table
with JSONB column json_field
. Each item in this column represents one uncomplicated dict, for example
{'first_field': 2 , 'second_field': 42}
Is there a way to create a new table if the dicts turned into columns?
My current approach looks like this:
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 concise way to do it? There are many fields in the JSONB column that I want to expand and the queries are getting cumbersome. I actually created a Python function that generates create / insert scripts. However, I would like there to be a good PostgreSQL way.
Any thoughts?
Edit
Below is a working solution based on feedback. 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;
source to share
Use the type normal_table
as the base type for the function jsonb_populate_record
:
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 you need to create id
for insertion 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
source to share
You can create a type (record) that maps 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 in the type, they are simply ignored. If there are fields in the type definition that do not match in the JSON document, they will null
.
source to share