In the article on SQL/JSON query functions we saw how JSON_TABLE converts JSON data to relational form. This article further explores various JSON_TABLE options for parsing and handling errors in JSON data.
Sample JSON Structure
For this demo consider JSON data containing an array of JSON objects.
Each JSON object has key name-value pairs:
- id – numeric unique identifier
- name – text
- skills – in the first few simpler examples, this is a text field (JSON Data 1); in the later examples (JSON Data 2), this is an array with any number of skills per JSON object
JSON Data 1:
[{
"id" : "101",
"name" : "Peter Parker",
"skills" : "web spinning, wall scaling"
}]
JSON Data 2:
[{
"id": "101",
"name": "Peter Parker",
"skills": [
"web spinning",
"wall scaling"
]
},
{
"id": "007",
"name": "James Bond",
"skills": [
"marksmanship",
"aviation",
"stealth"
]
}]
Let’s see how Oracle’s JSON_TABLE query operator handles the above structure for relational mapping and error handling.
Note: These scripts have been run on Oracle 12.1.
1. Simple JSON_TABLE query, no errors
JSON_TABLE reads values from the JSON key name-value pairs. Here’s a simple case with no errors in the data or JSON path:
SQL> -- Simple key name-value pairs
SQL> WITH json AS
2 (SELECT '[{
3 "id" : "101",
4 "name" : "Peter Parker",
5 "skills" : "web spinning, wall scaling"
6 }]' data
7 FROM DUAL)
8 SELECT id
9 , name
10 , skills
11 FROM json, JSON_TABLE(json.data, '$[*]'
12 COLUMNS (
13 id NUMBER PATH '$.id'
14 , name VARCHAR2(20) PATH '$.name'
15 , skills VARCHAR2(30) PATH '$.skills'
16 ));
ID NAME SKILLS
---- ------------------ ---------------------------
101 Peter Parker web spinning, wall scaling
2. Data type mismatch, EMPTY ON ERROR (default)
JSON_TABLE tries to read a column as NUMBER, but the data in the column is VARCHAR2.
What does JSON_TABLE do in this scenario?
SQL> -- Data type mismatch, default behavior (EMPTY ON ERROR)
SQL> -- NUMBER expected, got VARCHAR2
SQL> WITH json AS
2 (SELECT '[{
3 "id" : "A101",
4 "name" : "Peter Parker",
5 "skills" : "web spinning, wall scaling"
6 }]' data
7 FROM DUAL)
8 SELECT id
9 , name
10 , skills
11 FROM json, JSON_TABLE(json.data, '$[*]'
12 COLUMNS (
13 id NUMBER PATH '$.id'
14 , name VARCHAR2(20) PATH '$.name'
15 , skills VARCHAR2(30) PATH '$.skills'
16 ));
ID NAME SKILLS
---- ------------------ ------------------------------
Peter Parker web spinning, wall scaling
You’ll find that JSON_TABLE reports no error on the column with data type mismatch (ID in this case), it quietly nulls out the offending value.
That’s the default behavior of JSON_TABLE: EMPTY ON ERROR. This may or may not be desirable for your requirement. If this isn’t what you want and you’d rather know when there are data type issues, call ERROR ON ERROR to your aid.
3. Data type mismatch, ERROR ON ERROR
With the ERROR ON ERROR option on JSON_TABLE, and the same data type error as above, Oracle returns an error:
ORA-01722: invalid number
SQL> -- Data type mismatch, ERROR ON ERROR
SQL> -- NUMBER expected, got VARCHAR2
SQL> WITH json AS
2 (SELECT '[{
3 "id" : "A101",
4 "name" : "Peter Parker",
5 "skills" : "web spinning, wall scaling"
6 }]' data
7 FROM DUAL)
8 SELECT id
9 , name
10 , skills
11 FROM json, JSON_TABLE(json.data, '$[*]'
12 ERROR ON ERROR
13 COLUMNS (
14 id NUMBER PATH '$.id'
15 , name VARCHAR2(20) PATH '$.name'
16 , skills VARCHAR2(30) PATH '$.skills'
17 ));
FROM json, JSON_TABLE(json.data, '$[*]'
*
ERROR at line 11:
ORA-01722: invalid number
4. Key name-value pair missing in JSON, EMPTY ON ERROR (default)
Here’s another scenario: the PATH in JSON_TABLE refers to a key which is missing from the JSON data.
In the default EMPTY ON ERROR scenario, JSON_TABLE doesn’t mind this — it maps the SELECT column to NULL.
SQL> -- Key-name pair missing in JSON
SQL> -- Default (EMPTY ON ERROR) => treat as null
SQL> WITH json AS
2 (SELECT '[{
3 "id" : "101",
4 "name" : "Peter Parker",
5 }]' data
6 FROM DUAL)
7 SELECT id
8 , name
9 , skills
10 FROM json, JSON_TABLE(json.data, '$[*]'
11 COLUMNS (
12 id NUMBER PATH '$.id'
13 , name VARCHAR2(20) PATH '$.name'
14 , skills VARCHAR2(30) PATH '$.skills'
15 ));
ID NAME SKILLS
---- ------------------ -------------------------
101 Peter Parker
What if you want to report missing JSON keys as error? Once again, turn to the option ERROR ON ERROR.
5. Key name-value pair missing in JSON, ERROR ON ERROR
With ERROR ON ERROR, JSON_TABLE treats a missing key as an error:
ORA-40462: JSON_VALUE evaluated to no value
SQL> -- Key-name pair missing in JSON
SQL> -- ERROR ON ERROR => Treat as error
SQL> WITH json AS
2 (SELECT '[{
3 "id" : "101",
4 "name" : "Peter Parker",
5 }]' data
6 FROM DUAL)
7 SELECT id
8 , name
9 , skills
10 FROM json, JSON_TABLE(json.data, '$[*]'
11 ERROR ON ERROR
12 COLUMNS (
13 id NUMBER PATH '$.id'
14 , name VARCHAR2(20) PATH '$.name'
15 , skills VARCHAR2(30) PATH '$.skills'
16 ));
FROM json, JSON_TABLE(json.data, '$[*]'
*
ERROR at line 10:
ORA-40462: JSON_VALUE evaluated to no value
Things get tricky if you want to treat missing keys as NULL, but report all other errors. In Oracle 12.1, AFAIK there is no direct way to do it (if there is, I’ll be happy to learn of it).
In Oracle 12.2, this can be done with the ON EMPTY option. "ERROR ON ERROR NULL ON EMPTY" would report other errors and suppress the one for missing JSON keys.
6. Path to nested array with FORMAT JSON
For this test case and the ones after, we’ll change the value of key skills from text to array i.e.
"skills" : "web spinning, wall scaling"
to
"skills" : ["web spinning", "wall scaling"]
The SQL that we were using in the earlier examples no longer works after this change — it fails with the error:
ORA-40456: JSON_VALUE evaluated to non-scalar value
SQL> -- Path to nested array incorrect
SQL> -- ERROR ON ERROR => JSON_VALUE error
SQL> WITH json AS
2 (SELECT '[{
3 "id" : "101",
4 "name" : "Peter Parker",
5 "skills" : ["web spinning", "wall scaling"]
6 }]' data
7 FROM DUAL)
8 SELECT id
9 , name
10 , skills
11 FROM json, JSON_TABLE(json.data, '$[*]'
12 ERROR ON ERROR
13 COLUMNS (
14 id NUMBER PATH '$.id'
15 , name VARCHAR2(20) PATH '$.name'
16 , skills VARCHAR2(30) PATH '$.skills'
17 ));
FROM json, JSON_TABLE(json.data, '$[*]'
*
ERROR at line 11:
ORA-40456: JSON_VALUE evaluated to non-scalar value
An easy way out is to read the array formatted as JSON.
SQL> -- Path to nested array: FORMAT JSON option
SQL> -- Multiple JSON lines in array
SQL> WITH json AS
2 (SELECT '[{
3 "id" : "101",
4 "name" : "Peter Parker",
5 "skills" : ["web spinning", "wall scaling"]
6 },
7 {
8 "id" : "007",
9 "name" : "James Bond",
10 "skills" : ["marksmanship", "aviation", "stealth"]
11 }]' data
12 FROM DUAL)
13 SELECT id
14 , name
15 , skills
16 FROM json, JSON_TABLE(json.data, '$[*]'
17 ERROR ON ERROR
18 COLUMNS (
19 id NUMBER PATH '$.id'
20 , name VARCHAR2(20) PATH '$.name'
21 , skills VARCHAR2(40) FORMAT JSON PATH '$.skills'
22 ));
ID NAME SKILLS
---- --------------- ----------------------------------------
101 Peter Parker ["web spinning","wall scaling"]
7 James Bond ["marksmanship","aviation","stealth"]
7. ORDINALITY for row numbering
<column name> FOR ORDINALITY adds a numeric pseudocolumn to the JSON_TABLE result, containing a generated row number.
Here’s an example of including an "SNO" column based on JSON_TABLE FOR ORDINALITY.
SQL> -- FOR ORDINALITY to number the rows
SQL> WITH json AS
2 (SELECT '[{
3 "id" : "101",
4 "name" : "Peter Parker",
5 "skills" : ["web spinning", "wall scaling"]
6 },
7 {
8 "id" : "007",
9 "name" : "James Bond",
10 "skills" : ["marksmanship", "aviation", "stealth"]
11 }]' data
12 FROM DUAL)
13 SELECT sno
14 , id
15 , name
16 , skills
17 FROM json, JSON_TABLE(json.data, '$[*]'
18 ERROR ON ERROR
19 COLUMNS (
20 sno FOR ORDINALITY
21 , id NUMBER PATH '$.id'
22 , name VARCHAR2(20) PATH '$.name'
23 , skills VARCHAR2(40) FORMAT JSON PATH '$.skills'
24 ));
SNO ID NAME SKILLS
--- ---- ------------ ---------------------------------------
1 101 Peter Parker ["web spinning","wall scaling"]
2 7 James Bond ["marksmanship","aviation","stealth"]
8. Unnesting embedded array with NESTED PATH
In the examples so far, we retrieved the embedded array key value (skills) as-is, formatted as JSON. We can unnest the embedded array as well using the NESTED PATH syntax.
Here’s an example that unnests the embedded array, and also applies FOR ORDINALITY to the NESTED PATH COLUMNS. With this change:
- the skills array resolves into a new column SKILLNAME
- skills for each ID get an internal numbering (SKILLNO 1,2,3, etc)
SQL> -- Unnesting embedded array with NESTED PATH
SQL> -- Applying ordinality to inner values
SQL> WITH json AS
2 (SELECT '[{
3 "id" : "101",
4 "name" : "Peter Parker",
5 "skills" : ["web spinning", "wall scaling"]
6 },
7 {
8 "id" : "007",
9 "name" : "James Bond",
10 "skills" : ["marksmanship", "aviation", "stealth"]
11 }]' data
12 FROM DUAL)
13 SELECT id
14 , name
15 , skillno
16 , skillname
17 FROM json, JSON_TABLE(json.data, '$[*]'
18 COLUMNS (
19 id NUMBER PATH '$.id'
20 , name VARCHAR2(20) PATH '$.name'
21 , NESTED PATH '$.skills[*]'
22 COLUMNS (
23 skillno FOR ORDINALITY
24 , skillname VARCHAR2(30) PATH '$[0]')
25 ));
ID NAME SKILLNO SKILLNAME
---- --------------- ------- ------------------------------
101 Peter Parker 1 web spinning
101 Peter Parker 2 wall scaling
7 James Bond 1 marksmanship
7 James Bond 2 aviation
7 James Bond 3 stealth
Summary
This article described various options that can be used with JSON_TABLE query operator in Oracle.
Test cases demoed:
- Simple JSON_TABLE query, no errors
- Data type mismatch, EMPTY ON ERROR (default)
- Data type mismatch, ERROR ON ERROR
- Key name-value pair missing in JSON, EMPTY ON ERROR (default)
- Key name-value pair missing in JSON, ERROR ON ERROR
- Path to nested array with FORMAT JSON
- ORDINALITY for row numbering
- Unnesting embedded array with NESTED PATH
For further reading: How to Query JSON Data in Oracle: SQL/JSON Functions, Dot Notation. Check out the category JSON for all JSON-related articles on this site.
{ 2 comments… read them below or add one }
This article helped me a lot in using JSON_TABLE with its compact examples. Thanks.
could you please tell use what does it mean JSON_TABLE(json.data, ‘$[*]’
here i have not understand what these json.data is doing, because it is giving invalid identifier error to me.