postgresql stored procedure input json parsing

Question

I am new to PostgreSQL. Here is what I am trying to achieve. I will be getting a json as text as the input for the procedure and I need to parse them inside the procedure to get the data from them and save data into the tables.

Here is an example:

this is the table.

    CREATE TABLE json_test2
    (
      id serial primary key,
      name text,
      age int
    )

Now i am trying to create a procedure that will take a text as an input that contains json.

    {
        "data": [
            {
                "name": "kumar",
                "age": 12
            },
            {
                "name": "anand",
                "age": 25
            }
        ]
    }

please create a proc that will meet my requirement

Val K · Accepted Answer · 2015-10-20 21:10:06Z

7

Why do you need the proc, it's just Postgres function:

INSERT INTO table_name (name, age)
SELECT (rec->>'name')::text , (rec->>'age')::integer  FROM 
json_array_elements('{ "data": [ { "name": "kumar", "age": 12 }, { "name": "anand", "age": 25 } ] }'::json->'data' ) rec

See http://www.postgresql.org/docs/9.3/static/functions-json.html

Proc:

CREATE FUNCTION insert_from_json(in_json_txt json) RETURNS void AS
$BODY$
  INSERT INTO table_name (name, age)
  SELECT (rec->>'name')::text , (rec->>'age')::integer  FROM
  json_array_elements(in_json_txt->'data') rec
$BODY$
  LANGUAGE sql

edited Oct 20, 2015 at 21:10

answered Oct 20, 2015 at 20:11

Val K

4153 silver badges10 bronze badges

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

3 Comments

bharath kollareddy Over a year ago

can you please share the same information for the xml too if i have the same xml data how can we parse it and insert into the db?

Code Guru Over a year ago

can you share how you are calling the function

Val K Over a year ago

Just 'SELECT insert_from_json("jsontext goes here..."::json);' should work

Collectives™ on Stack Overflow

postgresql stored procedure input json parsing

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related