How to parse from an array extracted from JSON?

[sda]

I would like to parse a result extracted from a json that contains an array

My JSON looks like:

{"jsonrpc": "2.0", "id": null, "result": "[\"AZ-20240117-4017N5805616353S0WP\", \"KL-20240117-4017G5554333415S0AQ\", \"JK-20240117-4016V5221799491S0AC\", \"GH-20240117-4017H4507132996S0AQ\", \"QS-20240117-4017O0419049885S0AC\", \"OP-20240117-4017O0419094727S0AC\"]"},0

I am able to extract the ‘result’ array but don’t now how to convert it into a list

select j.* 
from   temp@InMemoryStorage l_json
join   ndjsontable
       ( passing l_json.contents_char --[*]
         columns result varchar2 path 'result'
       ) j

with result:

["AZ-20240117-4017N5805616353S0WP", "KL-20240117-4017G5554333415S0AQ", "JK-20240117-4016V5221799491S0AC", "GH-20240117-4017H4507132996S0AQ", "QS-20240117-4017O0419049885S0AC", "OP-20240117-4017O0419094727S0AC"]

I would like later to loop on the list created from the array to execute something.

Would someone know how to do this ?

[forums]

Please note that the JSON array is not a JSON array. It is a JSON-encoded JSON array.

For a JSON-array, something like the following would work:

select *
from   jsontable
       ( 'result[*]'
         passing '{"jsonrpc": "2.0", "id": null, "result": ["AZ-20240117-4017N5805616353S0WP", "KL-20240117-4017G5554333415S0AQ"]}'
         columns val varchar2 path '::self'
       )

To unwrap the JSON-encoded JSON-array, a construct like the following would work:

select j2.*
from   jsontable
       ( ''
         passing '{"jsonrpc": "2.0", "id": null, "result": "[\"AZ-20240117-4017N5805616353S0WP\", \"KL-20240117-4017G5554333415S0AQ\"]"'
         columns result varchar2 path 'result'
       ) j1
join   jsontable
       ( '[*]'
         passing j1.result
         columns row varchar2 path '::self'
       ) j2

The JSON-payload resembles the payload of Odoo. Please note that Invantive UniversalSQL comes with a robust Odoo-driver including high-performance extraction and loading tools.

[sda]

Indeed this is Odoo, but highly customized “on premises” 11.00 community version, this one does not come with an API.

We use our own built API nested inside Odoo to exchange data.

Thanks for the helping. Indeed a double join jsontable was the solution but i was not aware of the ::self

[forums]

OK, great to learn!