Good morning folks
I'm trying to consume JSON documents that basically follow this structure:
{
"items": [
{
"id": "111A",
"someAttributes": [
{
"name": "anAttribute",
"value": "A Value"
},
{
"name": "aDifferentAttribute",
"value": "A Different Value"
}
],
"someOtherAttributes": [
{
"name": "anotherAttribute",
"value": "Another Value"
},
{
"name": "lookAnAttribute",
"value": "Look, another value!"
}
]
]}
And, I need to get back:
ITEM_ID ATTR_NAME ATTR_VALUE
------------- ------------------------- ------------------------------
111A anAttribute A Value
111A anDifferentAttribute A Different Value
111A anotherAttribute Another Value
111A lookAnAttribute Look, another value!
Currently, I'm combining the results of two traversals of these documents, via json_table, like this:
SELECT item_id, attr_name, attr_value
FROM json_table(doc_json, '$.items[*]' COLUMNS (
item_id VARCHAR2 PATH '$.id',
NESTED PATH '$.someAttributes[*]' COLUMNS (
attr_name VARCHAR2 PATH '$.name',
attr_value VARCHAR2 PATH '$.value'
)
)
)
UNION ALL
SELECT item_id, attr_name, attr_value
FROM json_table(doc_json, '$.items[*]' COLUMNS (
item_id VARCHAR2 PATH '$.id',
NESTED PATH '$.someOtherAttributes[*]' COLUMNS (
attr_name VARCHAR2 PATH '$.name',
attr_value VARCHAR2 PATH '$.value'
)
)
);
It works. But, is Oracle doing two traversals of the JSON in this case? And, if so, is there any way to do the same thing in a single traversal? These documents can be quite large (> 500MB). I've considered using json_table chaining, but the actual contents of the "someAttributes" and "someOtherAttributes" object can be very large, often exceeding the 32KB extended max string size limit. Is my current solution my only option?
Thanks so much
Brian
Yes. For each union all subquery, you'll (full) scan the table again.
You can chain many nested paths in a single call to json_table. But for each new nesting, you'll get a new set of columns:
create table t (
doc_json varchar2(1000) check (doc_json is json)
);
insert into t values ('{
"items": [
{
"id": "111A",
"someAttributes": [
{
"name": "anAttribute",
"value": "A Value"
},
{
"name": "aDifferentAttribute",
"value": "A Different Value"
}
],
"someOtherAttributes": [
{
"name": "anotherAttribute",
"value": "Another Value"
},
{
"name": "lookAnAttribute",
"value": "Look, another value!"
}
]
}
]}');
commit;
with rws as (
SELECT j.*
FROM t, json_table(doc_json, '$.items[*]' COLUMNS (
item_id VARCHAR2 PATH '$.id',
NESTED PATH '$.someAttributes[*]' COLUMNS (
attr_name1 VARCHAR2 PATH '$.name',
attr_value1 VARCHAR2 PATH '$.value'
),
NESTED PATH '$.someOtherAttributes[*]' COLUMNS (
attr_name2 VARCHAR2 PATH '$.name',
attr_value2 VARCHAR2 PATH '$.value'
)
)
) j
)
select *
from rws;
ITEM_ID ATTR_NAME1 ATTR_VALUE1 ATTR_NAME2 ATTR_VALUE2
111A anAttribute A Value <null> <null>
111A aDifferentAttribute A Different Value <null> <null>
111A <null> <null> anotherAttribute Another Value
111A <null> <null> lookAnAttribute Look, another value!
Which is a problem if you want all the values to appear under one set of name/value columns.
Luckily this is easy to fix with a bit of unpivoting:
with rws as (
SELECT j.*
FROM t, json_table(doc_json, '$.items[*]' COLUMNS (
item_id VARCHAR2 PATH '$.id',
NESTED PATH '$.someAttributes[*]' COLUMNS (
attr_name1 VARCHAR2 PATH '$.name',
attr_value1 VARCHAR2 PATH '$.value'
),
NESTED PATH '$.someOtherAttributes[*]' COLUMNS (
attr_name2 VARCHAR2 PATH '$.name',
attr_value2 VARCHAR2 PATH '$.value'
)
)
) j
)
select item_id, attr_name, attr_value
from rws
unpivot ( (attr_name, attr_value) for src in (
(attr_name1, attr_value1),
(attr_name2, attr_value2)
)
);
ITEM_ID ATTR_NAME ATTR_VALUE
111A anAttribute A Value
111A aDifferentAttribute A Different Value
111A anotherAttribute Another Value
111A lookAnAttribute Look, another value!