Hi all,
DB Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Is this a JSON_TABLE bug, or am I doing something wrong?
When I run the following query:
SELECT role_id,
menu_id,
area_id,
item_id,
item_permission permission
FROM json_table('{
"PERMISSIONS": {
"ROLE_ID": "3",
"MENU": [
{
"ID": "82",
"CONTEXT": "SWI",
"CODE": "search_area_cell",
"PERMISSION": "TRUE",
"AREA": [
{
"ID": "1",
"CODE": "area",
"PERMISSION": [
"EXPORT",
"UPDATE",
"READ"
],
"ITEM": [
{
"ID": "1",
"CODE": "area_name",
"PERMISSION": [
"READ",
"UPDATE"
]
},
{
"ID": "2",
"CODE": "area_code",
"PERMISSION": "READ"
}
]
},
{
"ID": "2",
"CODE": "cell",
"PERMISSION": [
"READ",
"UPDATE"
],
"ITEM": [
{
"ID": "3",
"CODE": "cell_number"
},
{
"ID": "5",
"CODE": "cell_status"
},
{
"ID": "4",
"CODE": "cell_name",
"PERMISSION": [
"READ",
"UPDATE"
]
}
]
}
]
}
]
}
}',
'$.PERMISSIONS[*]' columns(role_id VARCHAR2(100) path '$.ROLE_ID',
NESTED path '$.MENU[*]' columns(menu_id VARCHAR2(100) path '$.ID',
NESTED path '$.AREA[*]' columns(area_id VARCHAR2(100) path '$.ID',
NESTED path '$.ITEM[*]' columns(item_id path '$.ID',
NESTED path '$.PERMISSION[*]' columns(item_permission path '$'))))))
WHERE item_permission IS NOT NULL;
I get the following result:
| ROLE_ID | MENU_ID | AREA_ID | ITEM_ID | PERMISSION |
|---|
| 3 | 82 | 1 | 1 | READ |
| 3 | 82 | 1 | 1 | UPDATE |
| 3 | 82 | 1 | 2 | READ |
| 3 | 82 | 2 | 4 | READ, UPDATE |
I'm not understanding why the permission for item_id "4" are not getting splitted.
I've found out that if I change the JSON permissions of item 2 from
{
"ID": "2",
"CODE": "area_code",
"PERMISSION": "READ"
}
to
{
"ID": "2",
"CODE": "area_code",
"PERMISSION": ["READ","UPDATE"]
}
Everything runs as expected.
Thanks in advance.