CODEWITHSUNDEEP
postgresqljsonb
pinei
pinei

Reputation: 2303

Function inserting json array elements into a table

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;

Upvotes: 3

Views: 1871

Answers (1)

klin
klin

Reputation: 121604

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)
$$;

Upvotes: 5

Related Questions