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?
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"
}
]
}
]
}