Applies to: SQL Server 2016 (13.x) and later versions
Azure SQL Database
Azure SQL Managed Instance
SQL 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
フラグメントが解析されて、各要素の 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
関数に提供されます。
このクエリの結果は次の表のようになります。
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_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 サポートを試用する」を参照してください。
次の例では、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.