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. and accessing JSON using the simple dot access notation.

Let’s look at the second aspect: type conversion when retrieving JSON. This post concerns Oracle Database 23ai (Free) and the simple dot access notation only. A future post will cover SQL JSON functions like JSON_EXISTS, JSON_TABLE, JSON_TRANSFORM, etc.

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. Oracle is no exception.

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;

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}}}

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 for each JSON. You can do that using the simple dot notation.

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

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

The numberOnly() method returned NULL, which is the default. If you prefer an error to be thrown in such cases you can set JSON_BEHAVIOR to ON_ERROR: ERROR instead.

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.