組み込み関数を使用した JSON データの検証、クエリ、変更 (SQL Server)

[アーティクル]
05/21/2024

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL データベース Azure SQL Managed Instance

JSON の組み込みのサポートには、次の組み込み関数が含まれています。この記事では、これらの関数について簡単に説明します。

ISJSON 。文字列に有効な JSON が含まれているかどうかをテストします。
JSON_VALUE 。JSON 文字列からスカラー値を抽出します。
JSON_QUERY 。JSON 文字列からオブジェクトまたは配列を抽出します。
JSON_MODIFY 。JSON 文字列内のプロパティの値を更新し、更新された JSON 文字列を返します。

すべての JSON 関数については、「JSON 関数」を参照してください。

この記事の Transact-SQL コードサンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプルデータベースを使用します。このサンプルデータベースは、Microsoft SQL Server サンプルとコミュニティプロジェクトのホームページからダウンロードできます。

このページの例の JSON テキスト

このページの例では、次の例に示す内容のような JSON テキストを使用します。

{
    "id": "DesaiFamily",
    "parents": [
        { "familyName": "Desai", "givenName": "Prashanth" },
        { "familyName": "Miller", "givenName": "Helen" }
    ],
    "children": [
        {
            "familyName": "Desai",
            "givenName": "Jesse",
            "gender": "female",
            "grade": 1,
            "pets": [
                { "givenName": "Goofy" },
                { "givenName": "Shadow" }
            ]
        },
        {
            "familyName": "Desai",
            "givenName": "Lisa",
            "gender": "female",
            "grade": 8
        }
    ],
    "address": {
        "state": "NY",
        "county": "Manhattan",
        "city": "NY"
    },
    "creationDate": 1431620462,
    "isRegistered": false
}

入れ子になった複雑な要素が含まれるこの JSON ドキュメントは、次のサンプルテーブルに格納されます。

CREATE TABLE Families (
    id INT identity CONSTRAINT PK_JSON_ID PRIMARY KEY,
    [doc] NVARCHAR(MAX)
);

JSON 関数は、JSON ドキュメントが varchar、nvarchar またはネイティブ json データ型のどちらに格納されているかに関係なく、同じように機能します。

ISJSON 関数を使用して JSON テキストを検証する

ISJSON 関数は、文字列に有効な JSON が含まれているかどうかをテストします。

次の例では、JSON 列に有効な JSON テキストが含まれる行が返されます。明示的な JSON 制約がない場合、nvarchar 列には任意のテキストを入力できます。

SELECT *
FROM Families
WHERE ISJSON(doc) > 0;

詳細については、「ISJSON」を参照してください。

JSON_VALUE 関数を使用して、JSON テキストから値を抽出する

JSON_VALUE 関数は、JSON 文字列からスカラー値を抽出します。次のクエリでは、id JSON フィールドが DesaiFamily の値と一致するドキュメントが、city および state JSON フィールドで並べ替えられて返されます。

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC,
    JSON_VALUE(f.doc, '$.address.state') ASC

このクエリの結果は次の表のようになります。

名前	都市	国
`DesaiFamily`	`NY`	`Manhattan`

詳細については、「JSON_VALUE」を参照してください。

JSON_QUERY 関数を使用して JSON テキストからオブジェクトまたは配列を抽出する

JSON_QUERY 関数は、JSON 文字列からオブジェクトまたは配列を抽出します。次の例では、クエリの結果には JSON フラグメントを返す方法を示します。

SELECT JSON_QUERY(f.doc, '$.address') AS Address,
    JSON_QUERY(f.doc, '$.parents') AS Parents,
    JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily';

このクエリの結果は次の表のようになります。

番地	Parents	Parent0
`{ "state": "NY", "county": "Manhattan", "city": "NY" }`	`[ { "familyName": "Desai", "givenName": "Prashanth" }, { "familyName": "Miller", "givenName": "Helen" } ]`	`{ "familyName": "Desai", "givenName": "Prashanth" }`

詳細については、「JSON_QUERY」を参照してください。

入れ子になった JSON コレクションを解析する

OPENJSON 関数を使用すると、JSON サブ配列を行セットに変換し、親要素と結合することができます。たとえば、すべてのファミリドキュメントを返し、それらを内部 JSON 配列として格納されている children オブジェクトと "結合" することができます。

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    c.givenName,
    c.grade
FROM Families f
CROSS APPLY OPENJSON(f.doc, '$.children') WITH (
    grade INT,
    givenName NVARCHAR(100)
) c

このクエリの結果は次の表のようになります。

名前	市区町村	givenName	grade
`DesaiFamily`	`NY`	`Jesse`	`1`
`DesaiFamily`	`NY`	`Lisa`	`8`

1 つの親行が、子サブ配列の 2 つの要素を解析することによって生成される 2 つの子行と結合されるため、2 つの行が取得されます。 OPENJSON 関数では、doc 列からの children フラグメントが解析されて、各要素の grade と givenName が行のセットとして返されます。この行セットを親ドキュメントと結合できます。

入れ子になった階層的な JSON サブ配列のクエリを実行する

入れ子になった JSON 構造のクエリを実行するために、CROSS APPLY OPENJSON の複数の呼び出しを適用できます。この例で使用される JSON ドキュメントには、children という名前の入れ子になった配列が含まれ、各子には pets の入れ子になった配列があります。次のクエリでは、各ドキュメントの子が解析されて、各配列オブジェクトが行として返された後、pets 配列が解析されます。

SELECT c.familyName,
    c.givenName AS childGivenName,
    p.givenName AS petName
FROM Families f
CROSS APPLY OPENJSON(f.doc) WITH (
    familyName NVARCHAR(100),
    children NVARCHAR(MAX) AS JSON
) AS a
CROSS APPLY OPENJSON(children) WITH (
    familyName NVARCHAR(100),
    givenName NVARCHAR(100),
    pets NVARCHAR(max) AS JSON
) AS c
OUTER APPLY OPENJSON(pets) WITH (givenName NVARCHAR(100)) AS p;

OPENJSON の最初の呼び出しでは、AS JSON 句を使用して children 配列のフラグメントが返されます。この配列フラグメントは、各子の givenName と firstName、および pets の配列を返す、2 番目の OPENJSON 関数に渡されます。 pets の配列は、ペットの givenName を返す 3 番目の OPENJSON 関数に提供されます。

このクエリの結果は次の表のようになります。

familyName	childGivenName	petName
`Desai`	`Jesse`	`Goofy`
`Desai`	`Jesse`	`Shadow`
`Desai`	`Lisa`	`NULL`

ルートドキュメントは、最初の OPENJSON(children) の呼び出しによって返される 2 つの children 行と結合されて、2 つの行 (またはタプル) が作成されます。その後、各行は、OUTER APPLY 演算子を使用して OPENJSON(pets) によって生成される新しい行と結合されます。 Jesse にはペットが 2 匹いるため、(Desai, Jesse) は Goofy および Shadow に対して生成される 2 つの行と結合されます。 Lisa にはペットがいないため、このタプルに対して OPENJSON(pets) が返す行はありません。ただし、OUTER APPLY を使用しているため、列には NULL が設定されます。 OUTER APPLY の代わりに CROSS APPLY を指定した場合、このタプルと結合できるペット行がないので、Lisa についての結果は返されません。

JSON_VALUE と JSON_QUERY を比較する

JSON_VALUE と JSON_QUERY の主な違いは、JSON_VALUE はスカラー値を返しますが、JSON_QUERY はオブジェクトまたは配列を返す点です。

次のようなサンプル JSON テキストがあるとします。

{
    "a": "[1,2]",
    "b": [1, 2],
    "c": "hi"
}

このサンプル JSON テキストでは、データメンバー "a" と "c" は文字列値ですが、データメンバー "b" は配列です。 JSON_VALUE と JSON_QUERY が返す結果は次のようになります。

Path	`JSON_VALUE` 戻り値	`JSON_QUERY` 戻り値
`$`	`NULL` またはエラー	`{ "a": "[1,2]", "b": [1, 2], "c": "hi" }`
`$.a`	`[1,2]`	`NULL` またはエラー
`$.b`	`NULL` またはエラー	`[1,2]`
`$.b[0]`	`1`	`NULL` またはエラー
`$.c`	`hi`	`NULL` またはエラー

AdventureWorks サンプルデータベースを使用して JSON_VALUE と JSON_QUERY をテストする

この記事で説明した組み込み関数をテストするには、AdventureWorks2022 サンプルデータベースを使用して次の例を実行します。スクリプトを実行してテストするための JSON データの追加方法について詳しくは、「組み込みの JSON サポートを試用する」を参照してください。

次の例では、SalesOrder_json テーブルの Info 列に JSON テキストが含まれています。

例 1: 標準の列と JSON データの両方を返す

次のクエリは、標準のリレーショナル列と JSON 列の両方から値を返します。

SELECT SalesOrderNumber,
    OrderDate,
    Status,
    ShipDate,
    AccountNumber,
    TotalDue,
    JSON_QUERY(Info, '$.ShippingInfo') ShippingInfo,
    JSON_QUERY(Info, '$.BillingInfo') BillingInfo,
    JSON_VALUE(Info, '$.SalesPerson.Name') SalesPerson,
    JSON_VALUE(Info, '$.ShippingInfo.City') City,
    JSON_VALUE(Info, '$.Customer.Name') Customer,
    JSON_QUERY(OrderItems, '$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0;

例 2: JSON 値の集計とフィルター

次のクエリは、(JSON に格納されている) 顧客名と (通常の列に格納されている) 状態別に小計を集計します。次に、(JSON に格納されている) 市区町村と (通常の列に格納されている) OrderDate で結果をフィルターします。

DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid = 3;
SET @city = N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer,
    Status,
    SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID = @territoryid
    AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
    AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'),
    Status
HAVING SUM(SubTotal) > 1000;

JSON_MODIFY 関数を使用して JSON テキストのプロパティ値を更新する

JSON_MODIFY 関数は、JSON 文字列内のプロパティの値を更新し、更新された JSON 文字列を返します。

次の例では、JSON を格納する変数の JSON プロパティの値を更新します。

SET @info = JSON_MODIFY(@jsonInfo, '$.info.address[0].town', 'London');

詳細については、「JSON_MODIFY」を参照してください。

次の方法で共有

組み込み関数を使用した JSON データの検証、クエリ、変更 (SQL Server)

このページの例の JSON テキスト

ISJSON 関数を使用して JSON テキストを検証する

JSON_VALUE 関数を使用して、JSON テキストから値を抽出する

JSON_QUERY 関数を使用して JSON テキストからオブジェクトまたは配列を抽出する

入れ子になった JSON コレクションを解析する

入れ子になった階層的な JSON サブ配列のクエリを実行する

JSON_VALUE と JSON_QUERY を比較する

AdventureWorks サンプルデータベースを使用して JSON_VALUE と JSON_QUERY をテストする

例 1: 標準の列と JSON データの両方を返す

例 2: JSON 値の集計とフィルター

JSON_MODIFY 関数を使用して JSON テキストのプロパティ値を更新する

フィードバック

その他のリソース

次の方法で共有

組み込み関数を使用した JSON データの検証、クエリ、変更 (SQL Server)

このページの例の JSON テキスト

ISJSON 関数を使用して JSON テキストを検証する

JSON_VALUE 関数を使用して、JSON テキストから値を抽出する

JSON_QUERY 関数を使用して JSON テキストからオブジェクトまたは配列を抽出する

入れ子になった JSON コレクションを解析する

入れ子になった階層的な JSON サブ配列のクエリを実行する

JSON_VALUE と JSON_QUERY を比較する

AdventureWorks サンプル データベースを使用して JSON_VALUE と JSON_QUERY をテストする

例 1: 標準の列と JSON データの両方を返す

例 2: JSON 値の集計とフィルター

JSON_MODIFY 関数を使用して JSON テキストのプロパティ値を更新する

関連するコンテンツ

フィードバック

その他のリソース

AdventureWorks サンプルデータベースを使用して JSON_VALUE と JSON_QUERY をテストする