Access nested JSON array that is double quoted

mwooldridge

How do I access the elements of TaskArray?

with q as (

select '[

  {

    "ID": 9801,

    "ComponentID": null,

    "Component": null,

    "OrderNumber": 1172,

    "GroupID": 3,

    "SubmittedDate": "10/03/2020 10:01",

    "ValidationStatus": "Approved",

    "TaskArray": "[{\"Stage\":\"Shelf\",\"TaskID\":\"9\",\"TaskName\":\"Build Virtual Server\",\"TaskTeam\":\"OSOS\"}]"}]' doc

 from dual)

select a.*

 from q,

    json_table(q.doc, '$[*]'

    columns

     identifier number path '$.ID',

     task_array clob path '$.TaskArray',

    nested '$.TaskArray'

     columns (

     stage             varchar2(250)  path '$.Stage')) a

/

IDENTIFIER TASK_ARRAY                          STAGE

---------- ------------------------------------------------------------ ----------

   9801 [{"Stage":"Shelf","TaskID":"9","TaskName":"Build Virtual Ser

1 row selected.

Beda Hammerschmidt-Oracle

Is it intended that the value for 'TaskArray' is a JSON string and not an array?

If it was a real array then this query would do the job

with q as (

select '[

{

"ID": 9801,

"ComponentID": null,

"Component": null,

"OrderNumber": 1172,

"GroupID": 3,

"SubmittedDate": "10/03/2020 10:01",

"ValidationStatus": "Approved",

"TaskArray": [{"Stage":"Shelf","TaskID":"9","TaskName":"Build Virtual Server","TaskTeam":"OSOS"}]}]' doc

from dual)

select a.*

from q,

json_table(q.doc, '$[*]'

columns

identifier number path '$.ID',

task_array clob format JSON path '$.TaskArray' ,

nested '$.TaskArray[*]'

columns (

stage varchar2(250) path '$.Stage')) a

/

kdario

There is no such thing as "JSON array that is double quoted".

You can have json array or json string.

In your case, "TaskArray" attribute is string, so you will need to convert it to json, something like this:

with q as (
select '[
  {
    "ID": 9801,
    "ComponentID": null,
    "Component": null,
    "OrderNumber": 1172,
    "GroupID": 3,
    "SubmittedDate": "10/03/2020 10:01",
    "ValidationStatus": "Approved",
    "TaskArray": "[{\"Stage\":\"Shelf\",\"TaskID\":\"9\",\"TaskName\":\"Build Virtual Server\",\"TaskTeam\":\"OSOS\"}]"}]' doc
 from dual)
select identifier, json_value(task_array, '$.Stage')
 from q,
    json_table(q.doc, '$[*]'
    columns
     identifier number path '$.ID',
     task_array clob path '$.TaskArray')