JSON_TABLE Options: Error Handling, Nested Path

January 30, 2018

in JSON, Pseudocolumns, SQL

JSON_TABLE Options

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:

  1. Simple JSON_TABLE query, no errors
  2. Data type mismatch, EMPTY ON ERROR (default)
  3. Data type mismatch, ERROR ON ERROR
  4. Key name-value pair missing in JSON, EMPTY ON ERROR (default)
  5. Key name-value pair missing in JSON, ERROR ON ERROR
  6. Path to nested array with FORMAT JSON
  7. ORDINALITY for row numbering
  8. 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.

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: