Forum Stats

  • 3,874,172 Users
  • 2,266,677 Discussions
  • 7,911,753 Comments

Discussions

Access nested JSON array that is double quoted

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.

Comments

  • Beda Hammerschmidt-Oracle
    Beda Hammerschmidt-Oracle Member Posts: 34 Employee

    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
    kdario Member Posts: 3,543 Silver Crown

    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')