CODEWITHSUNDEEP
arraysjsonpostgresqlstored-procedures
pavithra
pavithra

Reputation: 83

How to convert JSON into rows in Postgres stored procedure

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.

Upvotes: 0

Views: 372

Answers (1)

Jeremy
Jeremy

Reputation: 6723

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.

Upvotes: 2

Related Questions