A recent forum post prompted me to look into JSON syntax rules and their implications more closely. As per the JSON Developer’s Guide, there are two aspects to the topic of lax and strict JSON:
- When storing JSON as a database column
- Performing type conversions as part of JSON functions like JSON_QUERY, etc.
Let’s look at the second aspect: type conversion. This post concerns Oracle Database 23ai and JSON SQL functions.
Before diving into the third part let’s recap the gist about type conversions in JSON processing. If you read part 1 and 2 (link to part 1 | link to part 2), feel free to skip straight to SQL JSON functions.
Type conversions
JSON data is schemaless; in other words, it is very tolerant towards how you use it. There is a limited number of data types available but they aren’t always enforced. You can easily store a number as a string, and that’s perfectly fine. Many systems perform an implicit type conversion: they will try to cast the string to a number, and only if that fails an error is thrown.
Sometimes, however, you want finer control over how data is treated. If something is stored as a string ({ "a": "123" }), you may want to ensure that it is treated as such and not as a number.
Here is an example. Consider a bunch of JSON documents:
with sample_data(jcol) as (
values
(JSON('{ "a": { "b": { "c": "1" } } }')),
(JSON('{ "a": { "b": { "c": 2 } } }')),
(JSON('{ a: { b : { c: 3 } } }'))
)
select
rownum rn,
jcol
from
sample_data;
Keen eyes have spotted that the 3rd JSON document follows the “lax” syntax discussed in part 1.
If you run the query above, you get the following result:
RN J
_____ ________________________
1 {"a":{"b":{"c":"1"}}}
2 {"a":{"b":{"c":2}}}
3 {"a":{"b":{"c":3}}}
Even though { a: { b : { c: 3 } } } was passed verbatim like this – in other words, lax JSON – to the JSON constructor, it returned “strict” JSON. Also note how the first row’s “c” value is a string rather than a number. This will become important later on.
SQL JSON functions
The SQL standard has been enhanced in 2016 and 2023 to allow for better integration with JSON. Oracle Database has been one of the earliest adopters of the new standard functions, let’s look at some of them in more details. To keep this post reasonably short, it focuses only on those that allow the specification of a TYPE clause, that is, disable implicit type conversion.
The following functions feature a TYPE clause:
- JSON_VALUE
- JSON_QUERY
- JSON_TABLE
- JSON_EXISTS
- JSON_TRANSFORM
Let’s look at what that means using json_value as an example.
JSON_VALUE
Most often you use JSON_VALUE if you want to extract a scalar from a JSON document. You can alternatively select arrays or objects, but that requires the use of a collection (return) type and is out of scope of this article.
The following query provides sample data for the purpose of this discussion:
with sample_data(jcol) as (
values
(JSON('{ "a": { "b": { "c": "1" } } }')),
(JSON('{ "a": { "b": { "c": 2 } } }')),
(JSON('{ "a": { "b": { "c": 3 } } }'))
)
select
rownum rn,
jcol
from
sample_data;
Let’s assume you want to extract the numeric value of "c" – “1”, 2, and 3 in the example. Using json_value you can do that as follows:
with sample_data(jcol) as (
values
(JSON('{ "a": { "b": { "c": "1" } } }')),
(JSON('{ "a": { "b": { "c": 2 } } }')),
(JSON('{ "a": { "b": { "c": 3 } } }'))
)
select
rownum rn,
json_value(
jcol,
'$.a.b.c'
returning number
) as c,
dump(
json_value(
jcol,
'$.a.b.c'
returning number
)
) as type_c
from
sample_data;
Here is the query result. Typ=2 indicates a number, so we’re fine. json_value returns a VARCHAR2 by default, hence the need to tell it to return a number, as per the requirement.
RN C TYPE_C
_____ ____ _____________________
1 1 Typ=2 Len=2: 193,2
2 2 Typ=2 Len=2: 193,3
3 3 Typ=2 Len=2: 193,4
As you can see, Oracle implicitly converted the string "1" to a number. If you don’t want this conversion to happen, you tell json_value not to convert strings to numbers using the type (strict) clause. Parentheses are not optional by the way! The other option, type (lax) is the function’s default. Let’s see what that does:
with sample_data(jcol) as (
values
(JSON('{ "a": { "b": { "c": "1" } } }')),
(JSON('{ "a": { "b": { "c": 2 } } }')),
(JSON('{ "a": { "b": { "c": 3 } } }'))
)
select
rownum rn,
json_value(
jcol,
'$.a.b.c'
returning number
type (strict)
) as c,
dump(
json_value(
jcol,
'$.a.b.c'
returning number
type (strict)
)
) as type_c
from
sample_data;
The database returns the following:
RN C TYPE_C
_____ ____ _____________________
1 NULL
2 2 Typ=2 Len=2: 193,3
3 3 Typ=2 Len=2: 193,4
Now you can see that the string in row 1 wasn’t converted to a number, and you get a NULL in return; this is the default. If you want to be absolutely sure not to accidentally miss incorrect input data (because you’re rather safe than sorry), you can tell the database to throw an error using the error on error clause:
Error starting at line : 1 in command -
with sample_data(jcol) as (
values
(JSON('{ "a": { "b": { "c": "1" } } }')),
(JSON('{ "a": { "b": { "c": 2 } } }')),
(JSON('{ "a": { "b": { "c": 3 } } }'))
)
select
rownum rn,
json_value(
jcol,
'$.a.b.c'
returning number
error on error
type (strict)
) as c,
dump(
json_value(
jcol,
'$.a.b.c'
returning number
error on error
type (strict)
)
) as type_c
from
sample_data
Error at Command Line : 5 Column : 15
Error report -
SQL Error: ORA-03302: (ORA-01722 details) invalid string value: 1
ORA-61724: unable to convert value selected by JSON path '$.a.b.c'
ORA-01722: unable to convert string value containing '1' to a number: JCOL
The ability to change the behaviour on error is one of the major differences between SQL JSON functions and the simple dot access notation shown in a previous article. In scenarios where it is absolutely essential to ensure that data types match expectations type (strict), potentially combined with error on error comes in very handy.
By the way, you could have added a type conversion item method to the JSON path expression ($.a.b.c.numberOnly()) as well, but that’s not in scope of this article 🧐
Other SQL JSON functions
As you read earlier, there are additional SQL JSON function that accept the TYPE clause. Let’s look at a few examples, please refer to the JSON Developer’s Guide Part IV and the SQL Language Reference for Oracle Database 23ai as well.
Here is an example for JSON_QUERY and the TYPE clause.
with sample_data(jcol) as (
values
(JSON('{ "a": { "b": { "c": "1", "d": [ 1, 2, "3" ] } } }')),
(JSON('{ "a": { "b": { "c": 2 , "d": [ "1", 2, 3 ] } } }')),
(JSON('{ "a": { "b": { "c": 3 , "d": [ 1, 2, 3 ] } } }'))
)
select
rownum rn,
json_query(
jcol,
'$.a.b.d?(@ == 1)' type (lax)
) json_query,
json_query(
jcol,
'$.a.b.d?(@ == 1)' type (strict)
) json_query_strict
from
sample_data;
The database returns the following:
RN JSON_QUERY JSON_QUERY_STRICT
_____ _____________ ____________________
1 [1,2,"3"] [1,2,"3"]
2 ["1",2,3]
3 [1,2,3] [1,2,3]
As you can see the second row’s value of d ([ "1", 2, 3 ]) is discarded by json_query because it doesn’t contain the number 1 requested by the JSON Patch expression. it contains a string "1" that is implicitly cast by default or when specifying type (lax). Agagin, the type (strict) clause makes sense here because you ensure the input is in the exact form you want it.
JSON_EXISTS is a bit of an odd one, because you typically use it in where clauses and not in the select-list like the other functions you read about.
with sample_data(jcol) as (
values
(JSON('{ "a": { "b": { "c": "1" } } }')),
(JSON('{ "a": { "b": { "c": 2 } } }')),
(JSON('{ "a": { "b": { "c": 3 } } }'))
)
select
jcol
from
sample_data
where
json_exists (
jcol,
'$.a.b?(@.c in (1,2,3))' type (strict)
);
The strict type clause prevents the type conversion for the first JSON document { "c": "1" } does not match the JSON Path expression
JCOL
______________________
{"a":{"b":{"c":2}}}
{"a":{"b":{"c":3}}}
Another example, this time for JSON_TABLE. With this rather complex SQL function you transform JSON into a relational format. The below example is rather thorough, as it demonstrates how to break nested JSON down into relational table format. The TYPE clause can be applied to each column definition as you can see.
with sample_data(jcol) as (
values
(JSON('{ "a": { "b": { "c": "1", "d": [ { "id": 1, "val": 1 }, { "id": 2, "val": 2 } ] } } }')),
(JSON('{ "a": { "b": { "c": 2 , "d": [ { "id": 1, "val": 2 }, { "id": 2, "val": "3" } ] } } }')),
(JSON('{ "a": { "b": { "c": 3 , "d": [ { "id": 1, "val": "3" }, { "id": 2, "val": 4 } ] } } }'))
)
select
jt.*
from
sample_data s,
json_table(
s.jcol,
'$.a.b'
columns (
c_lax number,
c_strict number path '$.c' type (strict),
nested d[*] columns (
id number path '$.id',
val_lax number path '$.val',
val_strict number path '$.val' type (strict)
)
)
) jt
/
The database returns
C_LAX C_STRICT ID VAL_LAX VAL_STRICT
________ ___________ _____ __________ _____________
1 1 1
2 2 2
2 1 2 2
2 2 3
3 1 3
3 2 4 4
Again, those values provided as strings aren’t cast to numbers. As with all other SQL JSON functions the default is to return NULL in that case.
Summary
This post is veeeery long, I guess it’s most useful as a cheat sheet rather than something you read from the front to back. There is a lot to be said about JSON and the relational world, I hope this is a good start. If you have further examples you’d like to see, please let me know!