JSONの索引付け

通常、jsonデータ内部に含まれる少なくとも1つのフィールドが索引付けされる場合、その索引はjson索引です。json索引は、そのコンテンツの評価方法および問合せでの索引の使用方法の両方において強い型定義のデータの索引とよく似ています。ただし、jsonデータはスキーマがないため、索引付けされたjsonフィールドの型は表の行間で異なることがあります。現在、索引の宣言方法に応じて、このような型のバリエーションは部分的に許可されます。具体的には、jsonフィールドを指定する索引パスの後に、CREATE INDEX文の型宣言(ASキーワードを使用)を続ける必要があります。型は、jsonアトミック型(数値型、文字列、またはブール)のいずれか、またはanyAtomic型である必要があります。意味上、このような宣言は、行の索引パスの評価結果として、常に0個以上のアトミック項目が必要であることを意味します。その場合、順序内の各項目は同じ型または宣言された型のサブタイプを持つNULL (json null)になります。順序が空の場合、特殊な値EMPTYが索引に配置されます。

索引付けされたjsonフィールドの宣言型による制限に違反するデータを含む行が関連付けられた表にある場合、json索引の作成は失敗します。同様に、1つ以上の既存のjson索引によるこのような型制約に新規行が準拠しない場合、挿入/更新操作は拒否されます。

anyAtomicとしてのjson索引パスの宣言には、索引付けされたフィールドに様々な型の値を格納できるという明らかな利点があります。これらの値が索引に格納される場合、数値、文字列、ブールの順にソートされます。ただし、この型の自由度には、領域とCPUコストが伴います。これは、索引付きフィールドの任意の種類の数値が、索引に格納される前に数値にキャストされるためです。このキャストはCPU時間を要するため、結果の数値は通常、元の数値以外の値よりも大きくなります。

すべてのjsonパスがanyAtomicとして宣言されるjson索引は、型なしjson索引と呼ばれます。

非json索引と同様、型指定されたjson索引は単純キーまたは複数キーである可能性があります。これらについては、以降の項で説明します。ここに示す例は、すべてのユーザー情報をjsonデータとして格納するユーザー表に基づいています。具体的には、次のように作成された表を使用します。

CREATE TABLE users3 (id INTEGER, info JSON, PRIMARY KEY(id));

また、表users3に次のサンプル行(JSON形式で表示)が移入されることも前提とします。表users2と対照的に、これらのドキュメントが表users3に挿入された場合、"info"の下のサブドキュメントはjsonデータのままです。つまり、"info"サブドキュメント内のnull値は、表内にjsonのnull値として残ります。ただし、id 3の行では、(トップレベル)"info"列の値はSQL NULLになります。また、異なる行のデータのバリエーションにも注意してください。たとえば、info.address.phonesは通常配列ですが、id 4の行は単一のjsonオブジェクトです。

INSERT INTO users3 VALUES (
    0,
    {
        "income" : 1000,
        "address": {
            "street" : "somewhere",
            "city": "Boston",
            "state" : "MA",
            "phones" : [ 
                { "area":408, "number":50, "kind":"work" },
                { "area":415, "number":60, "kind":"work" },
                { "area":null, "number":52, "kind":"home" }
            ]
        },
        "expenses" : { "housing" : 1000, "clothes" : 230, "books" : 20 },
        "connections" : [ 100, 20, 20, 10, 20]
    }
);

INSERT INTO users3 VALUES (
    1,
    {
        "income" : null,
        "address": {
            "street" : "everywhere",
            "city": "San Fransisco",
            "state" : "CA",
            "phones" : [ 
                { "area":408, "number":50, "kind":"work" },
                { "area":408, "number":60, "kind":"home" },
                "4083451232"
            ]
        },
        "expenses" : { "housing" : 1000, "travel" : 300 },
        "connections" : [ ]
    }
);

INSERT INTO users3 VALUES (
    2,
    {
        "income" : 2000,
        "address": {
            "street" : "nowhere",
            "city": "San Jose",
            "state" : "CA",
            "phones" : [ ]
        },
        "expenses" : null,
        "connections" : null
    }
);

INSERT INTO users3 VALUES (3,{});

INSERT INTO users3 VALUES (
    4,
    {
        "address": {
            "street" : "top of the hill",
            "city": "San Fransisco",
            "state" : "CA",
            "phones" : { "area":408, "number":50, "kind":"work" }
        },
        "expenses" : { "housing" : 1000, "travel" : 300},
        "connections" : [ 30, 5, null ]
    }
);

INSERT INTO Users3 VALUES (
    5,
    {
        "address": {
            "street" : "end of the road",
            "city": "Portland",
            "state" : "OR"
        }
    }
);