Skip to Main Content
  • Questions
  • The JSON query results does not return the full path

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Bing.

Asked: January 18, 2021 - 7:53 pm UTC

Last updated: January 25, 2021 - 2:55 pm UTC

Version: 19C

Viewed 1000+ times

You Asked

Hi,

I'm try to evaluate the Json query. e.g I have following json string in the table
{
    "WO": [
      {
        "id": "1",
        "name": "WO1",
        "action": "add",
        "items": [
          {
            "id": "1",
            "author": "Tom Ask"
          },
          {
            "id": "2",
            "author": "Lee Frik"
          }
        ]
      },
      {
        "id": "3",
        "name": "WO1",
        "action": "add",
        "items": [
          {
            "id": "1",
            "author": "John A"

          },
          {
            "id": "2",
            "author": "Jean Pontus"
          }
        ]
      }
    ]
}


 select d.wo_data.WO.items.id from workorder_json d;


I get following results:
["Tom Ask","Lee Frik","John A","Jean Pontus"]


What I hope is return with full path like following
{
"WO": [
 "items": [
       {
        "author": "Tom Ask"
       },
       {
        "author": "Lee Frik"
       }
     ],
 "items": [
       {
        "author": "John A"
       },
       {
        "author": "Jean Pontus"
       }
     ]
  ]
}


Is there anyway to return like above?



and Chris said...

I believe not in 19c (at least in one function call).

You can use json_query to extract the items array:

create table t (
  c1 varchar2(1000)
    check ( c1 is json )
);

insert into t values ('{
    "WO": [
      {
        "id": "1",
        "name": "WO1",
        "action": "add",
        "items": [
          {
            "id": "1",
            "author": "Tom Ask"
          },
          {
            "id": "2",
            "author": "Lee Frik"
          }
        ]
      },
      {
        "id": "3",
        "name": "WO1",
        "action": "add",
        "items": [
          {
            "id": "1",
            "author": "John A"

          },
          {
            "id": "2",
            "author": "Jean Pontus"
          }
        ]
      }
    ]
}');

select json_query ( 
         c1, '$.WO.items[*]' 
           pretty 
           with array wrapper 
       )
from   t;

JSON_QUERY(C1,'$.WO.ITEMS[*]'PRETTYWITHARRAYWRAPPER)                                                                                                                                                             
[
  {
    "id" : "1",
    "author" : "Tom Ask"
  },
  {
    "id" : "2",
    "author" : "Lee Frik"
  },
  {
    "id" : "1",
    "author" : "John A"
  },
  {
    "id" : "2",
    "author" : "Jean Pontus"
  }
]   


But then you need to use json_object to reconstruct the attributes above it:

select json_object ( 
         'wo' value json_object ( 
           'items' value 
             json_query ( 
               c1, '$.WO.items[*]' 
                 pretty 
                 with array wrapper 
             )
         )
       )
from   t;

JSON_OBJECT('WO'VALUEJSON_OBJECT('ITEMS'VALUEJSON_QUERY(C1,'$.WO.ITEMS[*]'PRETTYWITHARRAYWRAPPER)))                                                                                                                               
{"wo":{"items":[
  {
    "id" : "1",
    "author" : "Tom Ask"
  },
  {
    "id" : "2",
    "author" : "Lee Frik"
  },
  {
    "id" : "1",
    "author" : "John A"
  },
  {
    "id" : "2",
    "author" : "Jean Pontus"
  }
]}}   


This is still not quite what you're looking for as the items are merged into one array. It may be possible to do this with some combination of the json*agg functions.

But there is a way in Oracle Database 21c. This added the json_transform function, which has many options to manipulate JSON.

One of these is the keep operation. This preserves items in the path you specify, including the path to it. Everything else is discarded:

JSON_TRANSFORM(C1,KEEP'$.WO.ITEMS'RETURNINGCLOBPRETTY)                                                                                                                                                                                                                                                                                                                                                                    
{
  "WO" :
  [
    {
      "items" :
      [
        {
          "id" : "1",
          "author" : "Tom Ask"
        },
        {
          "id" : "2",
          "author" : "Lee Frik"
        }
      ]
    },
    {
      "items" :
      [
        {
          "id" : "1",
          "author" : "John A"
        },
        {
          "id" : "2",
          "author" : "Jean Pontus"
        }
      ]
    }
  ]
} 

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Using JSON_ARRAYAGG

Kim Berg Hansen, January 19, 2021 - 10:52 am UTC

JSON_TRANSFORM will be great in 21c :-D

For 19c it's doable with JSON_ARRAYAGG as you suggest, Chris.

Split it to relational data with JSON_TABLE, keeping only the columns you want, and assemble the desired JSON again:

with workorder_json(wo_data) as (
   select
'{
    "WO": [
      {
        "id": "1",
        "name": "WO1",
        "action": "add",
        "items": [
          {
            "id": "1",
            "author": "Tom Ask"
          },
          {
            "id": "2",
            "author": "Lee Frik"
          }
        ]
      },
      {
        "id": "3",
        "name": "WO1",
        "action": "add",
        "items": [
          {
            "id": "1",
            "author": "John A"

          },
          {
            "id": "2",
            "author": "Jean Pontus"
          }
        ]
      }
    ]
}' from dual
)
select
   json_object(
      key 'WO'
      value json_arrayagg(
         json_object(
            key 'items'
            value json_arrayagg(
               json_object(j.author)
            )
         )
      )
   )
from workorder_json d, json_table(
   d.wo_data
 , '$.WO[*]'
   columns (
      wo_id number path '$.id'
    , nested path '$.items[*]' columns (
         item_id number path '$.id'
       , author varchar2(100) path '$.author'
      )
   )
) j
group by j.wo_id;


Or if the "id" columns cannot be trusted to be unique, it can be done with FOR ORDINALITY:

select
   json_object(
      key 'WO'
      value json_arrayagg(
         json_object(
            key 'items'
            value json_arrayagg(
               json_object(j.author)
            )
         )
      )
   )
from workorder_json d, json_table(
   d.wo_data
 , '$.WO[*]'
   columns (
      wo_id for ordinality
    , nested path '$.items[*]' columns (
         item_id for ordinality
       , author varchar2(100) path '$.author'
      )
   )
) j
group by j.wo_id;


The question IMHO is more what is the reason for returning it as JSON in this manner? But okay, if the result is to be pushed to front-end, it can make sense I guess. If it's to be further processed by the database, I'd guess working relational might be more sensible?

Anyway, that's up to the original posters use-case ;-)

Cheerio
/Kim
Chris Saxon
January 19, 2021 - 1:08 pm UTC

Great stuff, thanks Kim :)

A reader, January 19, 2021 - 2:27 pm UTC

Thanks Chris,

The SQL from your answer is generate the json_object response with fixed json schema.

I don't know the node "wo" and "items" node type is json_object or json_array when I query, we are working on URL query, it like following

http://host:port/workorder?fields=wo.items.name&status=failed

I'm query json document return fields = wo.items.name with condition where status="failed"

the generate SQL like following

select d.wo_data.wo.items.name as "root" from workorder_json d  Where json_value (wo_data,'$.status') = 'failed'



I'm evaluate the 19C JSON query feature compare with mongo DB, the Mongo DB can return the original Json parents node type
e.g.
there are two json Documents with different json schema.

to query wo.items.name it returns following two results.
{
  "wo": {
    "items": {
      "name": "Ask Tom"
    }
  }
}



{
"wo": [
 "items": [
       {
        "name": "Tom Ask"
       },
       {
        "name": "Lee Frik"
       }
  ]
}


Chris Saxon
January 25, 2021 - 2:55 pm UTC

So you want to know if an attribute is an object or array?

You can use the type method:

create table t (
  c1 varchar2(1000)
    check ( c1 is json )
);

insert into t values ( '{
  "wo": {
    "items": {
      "name": "Ask Tom"
    }
  }
}' );

insert into t values ( '{
"wo": {
"items": [
       {
        "name": "Tom Ask"
       },
       {
        "name": "Lee Frik"
       }
  ]
}
}' );

commit;

select t.c1.wo.items.type(), t.c1.wo.items
from   t t;

T.C1.WO.ITEMS.TYPE()    WO                                         
object                  {"name":"Ask Tom"}                          
array                   [{"name":"Tom Ask"},{"name":"Lee Frik"}] 


Then use this to pick json_object or json_array as appropriate.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.