How to write a Postgres function that receives a JSONB array value and iterates over it to insert the elements in a relational table?
JSON
[{"id": 1, "name": "John"},{"id": 2, "name": "Mary"}]
Table
CREATE TABLE Names (
id INTEGER,
name VARCHAR(50)
)
Function
CREATE FUNCTION
Insert_Names(IN data JSONB)
$$
FOR ...
INSERT INTO Names VALUES (...)
$$
LANGUAGE SQL;
Use jsonb_array_elements() to unpack the table and the operator ->> to get text values of the elements. Cast the values to appropriate types if necessary.
insert into names
select (elem->>'id')::int, elem->>'name'
from jsonb_array_elements('[{"id": 1, "name": "John"},{"id": 2, "name": "Mary"}]') as arr(elem)
returning *
id | name
----+------
1 | John
2 | Mary
(2 rows)
The function may look like this:
create or replace function insert_names(jsonb)
returns void language sql as $$
insert into names
select (elem->>'id')::int, elem->>'name'
from jsonb_array_elements($1) as arr(elem)
$$;
select insert_names('[{"id": 1, "name": "John"},{"id": 2, "name": "Mary"}]');
Alternatively, you can use the function jsonb_populate_recordset() which allows even simpler syntax:
create or replace function insert_names(jsonb)
returns void language sql as $$
insert into names
select *
from jsonb_populate_recordset(null::names, $1)
$$;
