JSON array parsing into rows

Question

I have JSON as below,

{
  "value": [
    {
      "id": "123",
      "createdDateTime": "2021-09-17T14:15:18Z"
    },
    {
      "id": "124",
      "createdDateTime": "2022-09-17T14:15:18Z"
    }
  ]
}

am trying to get the output as 2 separate records and store it in clob column(values), any help would be appreciated.

values

{"id": "123",
"createdDateTime": "2021-09-17T14:15:18Z"
}

{"id": "123",
"createdDateTime": "2021-09-17T14:15:18Z"
}

MT0 · Accepted Answer · 2021-09-21 09:20:05Z

1

You can use JSON_TABLE:

SELECT j.value
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.json_value,
         '$.value[*]'
         COLUMNS
           value VARCHAR2(4000) FORMAT JSON PATH '$'
       ) j

Which, for the sample data:

CREATE TABLE table_name (json_value CLOB CHECK (json_value IS JSON));

INSERT INTO table_name (json_value)
VALUES ('{"value":[{
            "id": "123",
            "createdDateTime": "2021-09-17T14:15:18Z"
            
        },
        {
            "id": "124",
            "createdDateTime": "2022-09-17T14:15:18Z"
            
        }]}')

Outputs:

VALUE

{"id":"123","createdDateTime":"2021-09-17T14:15:18Z"}

{"id":"124","createdDateTime":"2022-09-17T14:15:18Z"}

db<>fiddle here

answered Sep 21, 2021 at 9:20

MT0

173k12 gold badges70 silver badges136 bronze badges

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

1 Comment

Srini Over a year ago

Thank you, it works.

Collectives™ on Stack Overflow

JSON array parsing into rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related