A recent forum post prompted me to look into JSON syntax rules and their implications more closely. The way I read 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 the simple dot access notation only.

Here is the list of posts of this series:

• Part 1: storing JSON in Oracle Database 19c and 23ai
• Part 2: Dealing with type Conversions when using the simple dot access notation (this article)
• Part 3: Handling type conversions using JSON-SQL 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 I passed { a: { b : { c: 3 } } } – 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.

Accessing fields using the simple dot notation

Let’s assume you want to access $.a.b.c from each JSON. You can do that using the simple dot notation. Other ways of accessing "c" will be discussed later.

with sample_data(jcol) as (
    values
        (JSON('{ "a": { "b": { "c": "1" } } }')),
        (JSON('{ "a": { "b": { "c":  2 } } }')),
        (JSON('{ "a": { "b": { "c":  3 } } }'))
)
select
    sample.jcol.a.b.c
from
    sample_data sample;

You get the following result:

A      
______ 
"1"    
2      
3

The query returns JSON type instances, the database client converts these to strings and makes them human readable. Note for instance the first row returned: it’s a string. The other 2 are numbers. If you are unsure, use the type() item method:

with sample_data(jcol) as (
    values
        (JSON('{ "a": { "b": { "c": "1" } } }')),
        (JSON('{ "a": { "b": { "c":  2 } } }')),
        (JSON('{ "a": { "b": { "c":  3 } } }'))
)
select
    sample.jcol.a.b.c          as c,
    sample.jcol.a.b.c.type()   as type_c
from
    sample_data sample;

C      TYPE_C    
______ _________ 
"1"    string    
2      number    
3      number

Now, let’s assume you require $.a.b.c to be a number for some calculation. You can add a data-type conversion item method to your query:

with sample_data(jcol) as (
    values
        (JSON('{ "a": { "b": { "c": "1" } } }')),
        (JSON('{ "a": { "b": { "c":  2 } } }')),
        (JSON('{ "a": { "b": { "c":  3 } } }'))
)
select
    sample.jcol.a.b.c.numberOnly() as numbers_only
from
    sample_data sample;

As you can see, although “1” could previously be translated to a number (by way of string-to-number conversion), this is no longer permitted thanks to the numberOnly() item method.

   NUMBERS_ONLY 
_______________ 
                
              2 
              3

Note how the value is now NULL. You can find more details about item methods in the JSON Developer’s Guide

Summary

The simple dot access notation does precisely what its name implies: it allows you to access fields from a JSON document. It’s worth keeping in mind that each dot notation selects a part of a JSON. This can be an individual (= scalar) value if you provide an item method such as number(), string(), or date(). In that way, the expression is equivalent to the json_value SQL function (🔗 to json_value reference). If you omit the item method, you get a subset of the JSON, the same way you would with json_query (🔗 to json_query reference) or json_table (🔗 to json_table reference)SQL functions.

Since these have been mentioned several times, let’s look at them in the following post.