How to convert JSON into rows in Postgres stored procedure

Question

I am trying to write stored procedure in Postgres. But I have data as JSON in Postgres like the below.

{
    "identifiers": 
        [
            {
                "identifierType": "VIN",
                "value": "L608"
            },
            {
                "identifierType": "VIN",
                "value": "L604"
            }
       ]

Now I need to convert the above JSON into separate columns and rows using Postgres:

identifierType        value
-----------------------------
  VIN                  L608
  VIN                  L604

Please help! Thanks.

Jeremy · Accepted Answer · 2021-04-21 12:23:18Z

2

There's no need for a stored procedure to do this. In fact, a stored procedure couldn't return those data, although a function could.

Here's an example to return this data from a query:

-- Set up the test data
CREATE TABLE test (data json);

INSERT INTO test VALUES ('{"identifiers": 
 [
            {
                "identifierType": "VIN",
                "value": "L608"
            },
            {
                "identifierType": "VIN",
                "value": "L604"
            }
]}');


SELECT "identifierType", value 
FROM test
CROSS JOIN json_to_recordset(data->'identifiers') as x("identifierType" text, value text);

Here's a fiddle.

EDIT:

Here's a function that can do this. Note the a procedure will not work because you can't return data from a procedure.

CREATE OR REPLACE FUNCTION convert_my_json(p_data json)
RETURNS TABLE (
  "identifierType" text,
  "value" text
)
AS $$
  SELECT * FROM json_to_recordset(p_data->'identifiers') as x("identifierType" text, value text);
$$
LANGUAGE SQL
IMMUTABLE;

Updated fiddle.

edited Apr 21, 2021 at 12:23

answered Apr 20, 2021 at 12:21

Jeremy

6,77322 silver badges21 bronze badges

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

5 Comments

Frank Heikens Over a year ago

And a link to the manual: postgresql.org/docs/current/functions-json.html

pavithra Over a year ago

is there a way to create a stored procedure for this and call the procedure?

Jeremy Over a year ago

@pavithra I updated the answer and the fiddle with an example function.

pavithra Over a year ago

@Jeremy is it possible to use these retrieved JSON columns to my select query inside the same function? query 1 : select json_to_recordset query 2 : select query with columns retrieved from query 1. ( I want to use both query 1 and query 2 in same function, but return the result from query 2)

Jeremy Over a year ago

Probably? It would be better to open a new question and include the results you are trying to get. I can't tell from your description what you are trying to accomplish.

Collectives™ on Stack Overflow

How to convert JSON into rows in Postgres stored procedure

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related