Select all columns from Json Array string in oracle

Question

I have a json array string like below

[{a:100},{a:200},{a:300},{a:400}]

Passing it as a CLOB input parameter to the Stored procedure

How can i get a tabular output like below with in sql so that i can bulk insert in a table

Value
-----------
100
200
300
400

I have tried some examples mentioned at oracle docs but unable to find the working example with above mentioned output.

Thanks

MT0 · Accepted Answer · 2019-07-01 08:54:59Z

1

Use a JSON_TABLE:

Oracle Setup:

CREATE TABLE destination ( a NUMBER );

PL/SQL:

DECLARE
  your_input_parameter CLOB := '[{a:100},{a:200},{a:300},{a:400}]';
BEGIN
  INSERT INTO destination ( a )
    SELECT a
    FROM   JSON_TABLE(
             your_input_parameter,
             '$[*]'
             COLUMNS a NUMBER PATH '$.a'
           );
END;
/

Output:

SELECT * FROM destination;

|   A |
| --: |
| 100 |
| 200 |
| 300 |
| 400 |

db<>fiddle here

edited Jul 1, 2019 at 8:54

answered Jul 1, 2019 at 8:49

MT0

173k12 gold badges70 silver badges136 bronze badges

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

Collectives™ on Stack Overflow

Select all columns from Json Array string in oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related