This post is a troubleshooting story concerning Oracle Database 23ai, a common-table-expression (CTE), the values clause and an error in JSON_TABLE.
The Problem
This post aims at showing the steps undertaken to better understand how JSON_TABLE works in Oracle Database 23ai Free. If you would like to follow along, get the latest image from Oracle’s container registry – 23.6 at the time of writing – and started experimenting. The task at hand is to generate a number of small JSON documents on the fly, using a common-table-expression (CTE). These documents in turn are to be passed to JSON_TABLE, thus transforming a part of the JSON document into a relational format.
This post shows how to troubleshoot a specific issue by following a systematic troubleshooting approach. In this case an ORA-40491, thrown by JSON_TABLE, required a bit of investigation
Generate JSON “on the fly”
When researching bigger problems it typically pays off to break them down into smaller chunks. The same applies here: as per the introduction, JSON documents are to be generated and passed to JSON_TABLE. It is logical that the first step is to generate said JSON documents. One of the scenario’s constraints is to specifically avoid the JSON constructor. The JSON constructor accepts textual JSON (either lax or strict syntax), but it always returns JSON following strict syntax rules, which would have been problematic later on. Here is an example query generating JSON:
SQL> with sample_data(id, j) as (
2 values
3 (1, '{"a": "123", "b": 456, "c": {"d": 789}}'),
4 (2, '{"a": 123, "b": "456", "c": {"d": 789}}'),
5 (3, '{"a": 234, "b": "567", "c": {"d": 890}}')
6 )
7 select
8 s.id,
9 s.j
10 from
11 sample_data s
12 /
ID J
---------- ---------------------------------------
1 {"a": "123", "b": 456, "c": {"d": 789}}
2 {"a": 123, "b": "456", "c": {"d": 789}}
3 {"a": 234, "b": "567", "c": {"d": 890}}
Notice the use of the CTE and values clause, allowing you to quickly generate a few rows of data. When combining the values clause with CTEs it is necessary to provide column aliases. The query result looks like well-formed JSON, ticking the first box.
Adding JSON_TABLE to the query
With the JSON documents taken care of it is time to add JSON_TABLE to the query. To keep it simple only the value of the first field ist to be extracted. In JSON Path this translates to the value of '$.a' (see JSON Path Expression Syntax for more details). However, this didn’t work according to plan:
SQL> with sample_data(id, j) as (
2 values
3 (1, '{"a": "123", "b": 456, "c": {"d": 789}}'),
4 (2, '{"a": 123, "b": "456", "c": {"d": 789}}'),
5 (3, '{"a": 234, "b": "567", "c": {"d": 890}}')
6 )
7 select
8 id,
9 jt.a
10 from
11 sample_data s,
12 json_table(
13 s.j,
14 '$'
15 columns (
16 a varchar2(10) path '$.a'
17 )
18 ) jt
19 /
(3, '{"a": 234, "b": "567", "c": {"d": 890}}')
*
ERROR at line 5:
ORA-40491: invalid input data type for JSON_TABLE
Help: https://docs.oracle.com/error-help/db/ora-40491/
How’s that possible? This clearly is valid JSON! Is that error expected?
Troubleshooting
Let’s dive into the issue and find out what’s going on. JSON_TABLE expects an expression (expr) as its first argument. Here is an excerpt from the SQL Language Reference:
For expr, specify an expression that evaluates to a text literal. If expr is a column, then the column must be of data type VARCHAR2, CLOB, or BLOB.
The only explanation for the error is that sample_data.j isn’t a VARCHAR2 although it looks suspiciously like one. But is it? The dump() function can tell us more. Its output is very wide, which is why only the type information has been extracted, the rest is of no importance for this article:
SQL> with sample_data(id, j) as (
2 values
3 (1, '{"a": "123", "b": 456, "c": {"d": 789}}'),
4 (2, '{"a": 123, "b": "456", "c": {"d": 789}}'),
5 (3, '{"a": 234, "b": "567", "c": {"d": 890}}')
6 )
7 select
8 regexp_replace(
9 dump(s.j),
10 '(Typ=[[:digit:]]{2}).*',
11 '\1'
12 ) as data_type
13 from
14 sample_data s
15 /
DATA_TYPE
------------------------------------------
Typ=96
Typ=96
Typ=96
Table 2-1 in Oracle’s SQL Language Reference identifies type 96 as (N)CHAR. This might lead to something!
Solution
As it turned out a fixed-length CHAR column was previously passed to JSON_TABLE, which it understandably rejects. All that remains to be done is to convince JSON_TABLE that the JSON is of type VARCHAR2. Here is one way to do that, using the cast() function:
SQL> with sample_data(id, j) as (
2 values
3 (1, '{"a": "123", "b": 456, "c": {"d": 789}}'),
4 (2, '{"a": 123, "b": "456", "c": {"d": 789}}'),
5 (3, '{"a": 234, "b": "567", "c": {"d": 890}}')
6 )
7 select
8 id,
9 jt.a
10 from
11 sample_data s,
12 json_table(
13 cast (s.j as varchar2(4000)),
14 '$'
15 columns (
16 a varchar2(10) path '$.a'
17 )
18 ) jt
19 /
ID A
---------- ----------
1 123
2 123
3 234
There we go! Problem solved.
Well, actually …
Further testing based on the SQL query (not part of this article) required JSON to be provided in a textual format. Had that requirement not existed, this article probably wouldn’t have been written. Using the JSON constructor in the CTE wouldn’t have caused a problem, as you can see here:
SQL> with sample_data(id, j) as (
2 values
3 (1, JSON('{"a": "123", "b": 456, "c": {"d": 789}}')),
4 (2, JSON('{"a": 123, "b": "456", "c": {"d": 789}}')),
5 (3, JSON('{"a": 234, "b": "567", "c": {"d": 890}}'))
6 )
7 select
8 id,
9 jt.a
10 from
11 sample_data s,
12 json_table(
13 s.j,
14 '$'
15 columns (
16 a varchar2(10) path '$.a'
17 )
18 ) jt
19 /
ID A
---------- ----------
1 123
2 123
3 234
With Oracle Database 23ai and later, you are often better off using the JSON constructor. It does a lot of work behind the covers and is altogether an awesome time saver in most cases.