次の方法で共有


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

Applies to: SQL Server 2016 (13.x) and later versions Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

SQL データベース エンジンでの JSON の組み込みサポートには、次の関数が含まれています。

  • ISJSON tests whether a string contains valid JSON.
  • JSON_VALUE extracts a scalar value from a JSON string.
  • JSON_QUERY extracts an object or an array from a JSON string.
  • JSON_MODIFY updates the value of a property in a JSON string and returns the updated JSON string.

すべての 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 functions work the same whether the JSON document is stored in varchar, nvarchar, or the native json data type.

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

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

次の例では、JSON 列に有効な JSON テキストが含まれる行が返されます。 Without explicit JSON constraint, you can enter any text in the nvarchar column:

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

For more information, see ISJSON (Transact-SQL).

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

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

Name City County
DesaiFamily NY Manhattan

For more information, see 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';

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

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

For more information, see 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

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

Name City givenName grade
DesaiFamily NY Jesse 1
DesaiFamily NY Lisa 8

1 つの親行が、子サブ配列の 2 つの要素を解析することによって生成される 2 つの子行と結合されるため、2 つの行が取得されます。 OPENJSON 関数では、children 列からの doc フラグメントが解析されて、各要素の 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 配列のフラグメントが返されます。 この配列フラグメントは、各子の OPENJSONgivenName、および firstName の配列を返す、2 番目の pets 関数に渡されます。 pets の配列は、ペットの OPENJSON を返す 3 番目の givenName 関数に提供されます。

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

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

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

次の例では、Info テーブルの SalesOrder_json 列に 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');

For more information, see JSON_MODIFY.