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

対象者:SQL Server 2016 (13.x) およびそれ以降のバージョン Azure SQL DatabaseAzure SQL Managed InstanceSQL Database in Microsoft Fabric

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

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

すべての JSON 関数について、 JSON 関数 (Transact-SQL) を確認します。

この記事のコードサンプルは、AdventureWorks2025、AdventureWorksDW2025、またはAdventureWorksLT2025サンプルデータベースを使用しており、Azure Data SQL Samples Repository GitHubリポジトリからダウンロードできます。

このページの例の 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 (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

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

詳細については、「 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';

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

詳細については、 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

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

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

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

ルート ドキュメントは、最初の 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_VALUE と JSON_QUERY の主な違いは、JSON_VALUE がスカラー値を返すのに対し、JSON_QUERY がオブジェクトまたは配列を返す点です。

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

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

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

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

この記事で説明した組み込み関数をテストするには、AdventureWorks2025 サンプル データベースを使用して次の例を実行します。 スクリプトを実行して、テスト用の 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');

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

関連コンテンツ

• ISJSON (Transact-SQL)
• JSON_VALUE(Transact-SQL)
• JSON_QUERY(Transact-SQL)
• JSON_MODIFY(Transact-SQL)
• SQL Database エンジンの JSON パス式
• JSON データ型