Insert array of JSONB objects from one table as multiple rows in second table

Question

We are trying to migrate data from an array column containing JSONB to a proper Postgres table.

{{"a":1,"b": 2, "c":"bar"},{"a": 2, "b": 3, "c":"baz"}}

 a |    b    | c
---+---------+---
 1 | 2        | "bar"
 2 | 3        | "baz"

As part of the process, we have made several attempts using functions like unnest and array_to_json. In the unnest case, we get several JSONB rows, but cannot figure out how to insert them into the second table. In the array_to_json case, we are able to cast the array to a JSON string, but the json_to_recordset does not seem to accept the JSON string from a common table expression.

What would be a good strategy to 'mirror' the array of JSONB items as a proper table, so that we can run the query inside of a stored procedure, triggered on insert?

Is the structure of the table (number, names and types of columns) known? — klin
– klin, Commented Jan 18, 2019 at 15:09
Yes, we have defined a second table with proper data types, and provided a schema mapping to the json_to_recordset call, as per the documentation. — Brylie Christopher Oxley
– Brylie Christopher Oxley, Commented Jan 18, 2019 at 15:26
After a bit more searching, the following looks relevant, although I don't fully understand why to use "cross join lateral" reddit.com/r/PostgreSQL/comments/2u6ah3/… — Brylie Christopher Oxley
– Brylie Christopher Oxley, Commented Jan 18, 2019 at 15:29

klin · Accepted Answer · 2019-01-18 15:53:47Z

1

Use unnest() in a lateral join:

with my_data(json_column) as (
values (
    array['{"a":1,"b":2,"c":"bar"}','{"a":2,"b":3,"c":"baz"}']::jsonb[])
)
select 
    value->>'a' as a, 
    value->>'b' as b, 
    value->>'c' as c
from my_data
cross join unnest(json_column) as value

 a | b |  c  
---+---+-----
 1 | 2 | bar
 2 | 3 | baz
(2 rows)

You may need some casts or converts, e.g.:

select 
    (value->>'a')::int as a, 
    (value->>'b')::int as b, 
    (value->>'c')::text as c
from my_data
cross join unnest(json_column) as value

Lateral joining means that the function unnest() will be executed for each row from the main table. The function returns elements of the array as value.

edited Jan 18, 2019 at 15:53

answered Jan 18, 2019 at 15:36

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

klin Over a year ago

Ok, I did not think anyone could use such a strange construction as jsonb[]. It's definitely a kind of anti-pattern. See also: stackoverflow.com/a/46015221/1995738 and stackoverflow.com/a/37209754/1995738

Brylie Christopher Oxley Over a year ago

Thanks for your feedback. I will try your suggestions ASAP.

Collectives™ on Stack Overflow

Insert array of JSONB objects from one table as multiple rows in second table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related