Microsoft SQL Server
SQL ServerのJSON

構文

• JSON_VALUE （expression、path） - JSON文字列からスカラー値を抽出します。
• JSON_QUERY （expression [、path]） - JSON文字列からオブジェクトまたは配列を抽出します。
• OPENJSON （jsonExpression [、path]） - JSONテキストを解析し、JSONのオブジェクトとプロパティを行と列として返すテーブル値関数です。
• ISJSON （expression） - 文字列に有効なJSONが含まれているかどうかをテストします。
• JSON_MODIFY （expression、path、newValue） - JSON文字列のプロパティの値を更新し、更新されたJSON文字列を返します。

パラメーター

備考

ALTER DATABASE <Database-Name-Here> SET COMPATIBILITY_LEVEL = 130

FOR JSONでの照会結果のJSON形式のフォーマット

入力テーブルのデータ（人物テーブル）

クエリ

SELECT Id, Name, Age
FROM People
FOR JSON PATH

結果

[
    {"Id":1,"Name":"John","Age":23},
    {"Id":2,"Name":"Jane","Age":31}
]

JSONテキストを解析する

JSON_VALUE関数とJSON_QUERY関数は、JSONテキストを解析し、パス上のスカラー値またはオブジェクト/配列をJSONテキストで返します。

DECLARE @json NVARCHAR(100) = '{"id": 1, "user":{"name":"John"}, "skills":["C#","SQL"]}'

SELECT
    JSON_VALUE(@json, '$.id') AS Id,
    JSON_VALUE(@json, '$.user.name') AS Name,
    JSON_QUERY(@json, '$.user') AS UserObject,
    JSON_QUERY(@json, '$.skills') AS Skills,
    JSON_VALUE(@json, '$.skills[0]') AS Skill0

結果

CROSS APPLY OPENJSONを使用して、親と子のJSONエンティティに参加する

親オブジェクトを子エンティティと結合します。たとえば、各自の関係テーブルとその趣味が必要です。

DECLARE @json nvarchar(1000) =
N'[
    {
        "id":1,
        "user":{"name":"John"},
        "hobbies":[
            {"name": "Reading"},
            {"name": "Surfing"}
        ]
    },
    {
        "id":2,
        "user":{"name":"Jane"},
        "hobbies":[
            {"name": "Programming"},
            {"name": "Running"}
        ]
    }
 ]'

クエリ

SELECT 
    JSON_VALUE(person.value, '$.id') as Id,
    JSON_VALUE(person.value, '$.user.name') as PersonName,
    JSON_VALUE(hobbies.value, '$.name') as Hobby
FROM OPENJSON (@json) as person
    CROSS APPLY OPENJSON(person.value, '$.hobbies') as hobbies

あるいは、この問合せはWITH句を使用して記述できます。

SELECT 
    Id, person.PersonName, Hobby
FROM OPENJSON (@json)
WITH(
    Id int '$.id',
    PersonName nvarchar(100) '$.user.name',
    Hobbies nvarchar(max) '$.hobbies' AS JSON
) as person
CROSS APPLY OPENJSON(Hobbies)
WITH(
    Hobby nvarchar(100) '$.name'
)

結果

計算カラムを使用したJSONプロパティのインデックス

SQL ServerにJSONドキュメントを格納する際には、JSONドキュメントのプロパティに関するクエリ結果を効率的にフィルタリングおよびソートできる必要があります。

CREATE TABLE JsonTable
(
    id int identity primary key,
    jsonInfo nvarchar(max),
    CONSTRAINT [Content should be formatted as JSON]
    CHECK (ISJSON(jsonInfo)>0)
)

INSERT INTO JsonTable
VALUES(N'{"Name":"John","Age":23}'),
(N'{"Name":"Jane","Age":31}'),
(N'{"Name":"Bob","Age":37}'),
(N'{"Name":"Adam","Age":65}')
GO

与えられたテーブルが与えられた場合= 'Adam'という名前の行を探したい場合は、次のクエリを実行します。

SELECT * 
FROM JsonTable Where 
JSON_VALUE(jsonInfo, '$.Name') = 'Adam'

しかし、これは、大規模なテーブル上の効率的ではない完全なテーブルを実行するSQLサーバーが必要になります。

これをスピードアップするために、インデックスを追加したいと思いますが、JSONドキュメントのプロパティを直接参照することはできません。解決策は、JSONパス$.Nameに計算カラムを追加してから、計算カラムにインデックスを追加することです。

ALTER TABLE JsonTable
ADD vName as JSON_VALUE(jsonInfo, '$.Name')

CREATE INDEX idx_name
ON JsonTable(vName)

今度は、同じクエリを実行するときに、フルテーブルスキャンではなくSQL Serverがインデックスを使用して、非クラスタ化インデックスを検索し、指定された条件を満たす行を見つけます。

注意：SQL Serverでインデックスを使用するには、クエリで使用する式と同じ式JSON_VALUE(jsonInfo, '$.Name')この例ではJSON_VALUE(jsonInfo, '$.Name')を使用して計算列を作成する必要がありますが、計算列vName

FOR JSONを使用して1つのJSONオブジェクトとして1つのテーブル行をフォーマットする

FOR JSON句にWITHOUT_ARRAY_WRAPPERオプションを指定すると、JSON出力から配列括弧が削除されます。これは、クエリで単一行を返す場合に便利です。

注：複数の行が返された場合、このオプションは無効なJSON出力を生成します。

入力テーブルのデータ（人物テーブル）

クエリ

SELECT Id, Name, Age
FROM People
WHERE Id = 1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

結果

{"Id":1,"Name":"John","Age":23}

OPENJSON関数を使用してJSONテキストを解析する

OPENJSON関数はJSONテキストを解析し、複数の出力を返します。返される値は、WITH句で定義されたパスを使用して指定されます。一部の列にパスが指定されていない場合は、その列名がパスとして使用されます。この関数は、返された値をWITH句で定義されたSQL型にキャストします。何らかのオブジェクト/配列を返す必要がある場合は、JSONオプションを列定義に指定する必要があります。

DECLARE @json NVARCHAR(100) = '{"id": 1, "user":{"name":"John"}, "skills":["C#","SQL"]}'

SELECT * 
FROM OPENJSON (@json)
    WITH(Id int '$.id',
        Name nvarchar(100) '$.user.name',
        UserObject nvarchar(max) '$.user' AS JSON,
        Skills nvarchar(max) '$.skills' AS JSON,
        Skill0 nvarchar(20) '$.skills[0]')

結果