Parsing JSON in Postgresql

programming, snippet

Say we have a table called person with the following structure

---------------------------
column| datatype
---------------------------
 _id  | autonumber
 name | text
 age  | integer
---------------------------

We want to insert multiple records into the table using a stored procedure. If we have a stored procedure with say two input variables name and age, then it will take multiple calls. We can do this using a single call if we use a json input and using the json_to_recordset function.

CREATE OR REPLACE FUNCTION public.ins_person(
	json)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$

BEGIN
	insert into person(name, age)
	select x.name, x.age from json_to_recordset($1) x 
	(
    	name text,
 		age int	
	);       
   END;
$BODY$;

We test it out with the following JSON

    [
        {"name": "Albert E", "age": "20"}, 
        {"name": "Ramanujam", "age": "27"}
    ]

Lets execute the stored procedure:

SELECT public.ins_person(
	'[
        {"name": "Albert E", "age": "20"}, 
        {"name": "Ramanujam", "age": "27"}
    ]'
);

This will insert couple of rows into the person table.

That’s it.

Other postgresql articles