parse string of json array using Oracle dbms_json

Question

in oracle 19c plsql i have such string

[{"Books":"book1","pages":"100"},{"Books":"book1","pages":"200"}]

i want to parse it using dbms_json to get table

Books	pages
book1	100
book2	200

how to do this? anyone can help?

No PL/SQL required: stackoverflow.com/a/59857542

user330315
– user330315

2021-06-24 20:25:51 +00:00
Commented Jun 24, 2021 at 20:25 — user330315
– user330315, Commented Jun 24, 2021 at 20:25

EJ Egyed · Accepted Answer · 2021-06-25 12:26:50Z

0

You can use the JSON_TABLE function to take a JSON string and convert it to a table structure.

SELECT *
  FROM JSON_TABLE ('[{"Books":"book1","pages":"100"},{"Books":"book1","pages":"200"}]',
                   '$[*]'
                   COLUMNS books PATH '$.Books', pages PATH '$.pages');

answered Jun 25, 2021 at 12:26

EJ Egyed

6,1341 gold badge11 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

parse string of json array using Oracle dbms_json

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related