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

適用対象: 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 ドキュメントが varcharnvarchar またはネイティブ 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 フラグメントが解析されて、各要素の gradegivenName が行のセットとして返されます。 この行セットを親ドキュメントと結合できます。

入れ子になった階層的な 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 配列のフラグメントが返されます。 この配列フラグメントは、各子の givenNamefirstName、および 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_VALUEJSON_QUERY の主な違いは、JSON_VALUE はスカラー値を返しますが、JSON_QUERY はオブジェクトまたは配列を返す点です。

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

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

このサンプル JSON テキストでは、データ メンバー "a" と "c" は文字列値ですが、データ メンバー "b" は配列です。 JSON_VALUEJSON_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」を参照してください。