Oracle DatabaseのJSON strictおよびlax構文| Pt 3: 型変換(SQL関数) (2025/03/14)

https://blogs.oracle.com/developers/post/json-strict-and-lax-syntax-in-oracle-database-pt-3-type-conversions-sql-functions

投稿者：Martin Bach

最近のフォーラム投稿では、JSON構文ルールとその意味をより詳しく調べるようになりました。JSON開発者ガイドによると、laxとstrict JSONのトピックには2つの側面があります。

• JSONをデータベース列として格納する場合
• JSON_QUERYなどのJSONファンクションの一部として型変換を実行

2つ目の側面(型変換)を見てみましょう。この投稿は、Oracle Database 23aiおよびJSON SQL関数に関係しています。

3番目の部分に飛び込む前に、JSON処理での型変換に関する要点をまとめてみましょう。パート1とパート2(パート1へのリンク|パート2へのリンク)を読む場合は、SQL JSON関数に直接スキップしてください。

型変換

JSONデータはスキーマレスです。言い換えれば、その使用法に対して非常に寛容です。使用可能なデータ型の数は限られていますが、必ずしも強制されるわけではありません。数値を文字列として簡単に格納でき、これは完全に問題ありません。多くのシステムでは暗黙的な型変換が実行されます。文字列を数値にキャストしようとしますが、失敗した場合にのみエラーがスローされます。

ただし、データの処理方法をより詳細に制御する必要がある場合があります。何かが文字列({ "a": "123" })として格納されている場合、それが数値ではなくそのように扱われるようにすることができます。

次に例を示します。多数のJSONドキュメントについて考えてみます。

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は、第3のJSONドキュメントが第1部で説明した「緩い」構文に従うことを発見しました。

前述の問合せを実行すると、次の結果が表示されます。

RN J                        
_____ ________________________ 
    1 {"a":{"b":{"c":"1"}}}    
    2 {"a":{"b":{"c":2}}}      
    3 {"a":{"b":{"c":3}}}

{ a: { b : { c: 3 } } } }はこんな動詞で JSONコンストラクタにlax JSONが渡されましたが、"strict" JSONが返されました。また、最初の行の「c」値が数値ではなく文字列であることにも注意してください。これは後で重要になります。

SQL JSONファンクション

SQL標準は2016年と2023年に拡張され、JSONとの統合が向上しました。Oracle Databaseは、新しい標準機能の最も初期の導入者の1つです。詳細については、その一部を見てみましょう。このポストを合理的に短く保つためには、TYPE句を指定できるもの、つまり暗黙的な型変換を無効にするもののみに焦点を当てます。

次の関数は、TYPE句を備えています。

• JSON_VALUE
• JSON_QUERY
• JSON_TABLE
• JSON_EXISTS
• JSON_TRANSFORM

json_valueを例として使用することの意味を見てみましょう。

JSON_VALUE

JSONドキュメントからスカラーを抽出する場合は、ほとんどの場合JSON_VALUEを使用します。配列またはオブジェクトを選択することもできますが、コレクション(戻り)型の使用が必要で、この記事の範囲外です。

次の問合せでは、この説明の目的でサンプル・データを提供します。

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;

この例では、「c」- 「1」、「2」および「3」の数値を抽出するとします。json_valueを使用すると、次のように実行できます。

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;

問合せ結果は次のとおりです。Typ=2は数値を示すため、問題ありません。json_valueはデフォルトでVARCHAR2を返すため、要件に従って数値を返すように指示する必要があります。

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

ご覧のとおり、Oracleは暗黙的に文字列1を数値に変換しました。この変換を実行しない場合は、json_valueにtype (strict)句を使用して文字列を数値に変換しないように指示します。カッコはオプションではありません! もう1つのオプションtype (lax)が関数のデフォルトです。何をするかを見てみましょう:

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;

データベースは次のものを返します。

RN    C TYPE_C                
_____ ____ _____________________ 
    1      NULL                  
    2    2 Typ=2 Len=2: 193,3    
    3    3 Typ=2 Len=2: 193,4

これで、行1の文字列が数値に変換されておらず、NULLが返されることがわかります。これがデフォルトです。間違った入力データを誤って見逃さないようにしたい場合(ごめんなさいより安全であるため)、error on error句を使用してエラーをスローするようにデータベースに指示できます。

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

エラー時の動作を変更する機能は、SQL JSON関数と、前の記事で示した単純なドット・アクセス表記法との主な違いの1つです。データ型が期待される型(厳密)と一致するようにすることが絶対に不可欠なシナリオでは、エラー時にエラーと組み合せる可能性が非常に便利です。

ちなみに、JSONパス式($.a.b.c.numberOnly())にも型変換項目メソッドを追加できましたが、この記事の範囲にはありません 🧐

その他のSQL JSON関数

前述のとおり、TYPE句を受け入れる追加のSQL JSONファンクションがあります。いくつかの例を見てみましょう。JSON開発者ガイドのパートIVおよびOracle Database 23aiのSQL言語リファレンスも参照してください。

JSON_QUERYおよびTYPE句の例を次に示します。

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;

データベースは次のものを返します。

RN JSON_QUERY    JSON_QUERY_STRICT    
_____ _____________ ____________________ 
    1 [1,2,"3"]     [1,2,"3"]            
    2 ["1",2,3]                          
    3 [1,2,3]       [1,2,3]

2行目のdの値([ "1"、 2、 3 ])は、JSONパッチ式でリクエストされた数値1が含まれていないため、json_queryによって破棄されます。これには、デフォルトで暗黙的にキャストされる文字列"1"または型(lax)を指定するときが含まれます。Agaginでは、入力が正しい形式であることを確認するため、ここではtype (strict)句が意味を持ちます。

JSON_EXISTSは少し奇数です。これは、通常、ここで句を使用し、他の関数と同様にselect-listで使用しないためです。

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)
    );

strict型句により、最初のJSONドキュメント{ "c": "1" }の型変換がJSONパス式と一致しません

JCOL                   
______________________ 
{"a":{"b":{"c":2}}}    
{"a":{"b":{"c":3}}}

別の例として、今回はJSON_TABLEです。この非常に複雑なSQL関数では、JSONをリレーショナル形式に変換します。次の例では、ネストされたJSONをリレーショナル表形式に分割する方法を示しているため、かなり詳しく説明しています。TYPE句は、各列定義に適用できます。

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
/

データベースが返す

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

また、文字列として指定された値は数値にキャストされません。他のすべてのSQL JSON関数と同様に、デフォルトではNULLが返されます。

まとめ

この投稿はとても長いので、前から後ろに読むものではなく、チートシートとして最も役に立つと思います。JSONとリレーショナル世界について言いたいことはたくさんありますが、これが良いスタートになることを願っています。もっと見たい例があれば教えてください!