Microsoft SQL Server => JSONデータを使用したクエリ

クエリでJSONの値を使用する

JSON_VALUEファンクションを使用すると、2番目の引数として指定されたパスのJSONテキストからデータを取得し、selectクエリの任意の部分でこの値を使用できます。

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'

レポートでのJSON値の使用

JSONテキストからJSON値を抽出したら、クエリの任意の部分でJSON値を使用できます。グループ化集計などを使用してJSONデータに関するレポートを作成できます。

select JSON_VALUE(Data, '$.Type') as type,
        AVG( cast(JSON_VALUE(Data, '$.ManufacturingCost') as float) ) as cost
from Product
group by JSON_VALUE(Data, '$.Type') 
having JSON_VALUE(Data, '$.Type') is not null

クエリ結果から悪いJSONテキストをフィルタアウトする

一部のJSONテキストが正しくフォーマットされていない場合は、ISJSON関数を使用してこれらのエントリをクエリから削除できます。

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'
and ISJSON(Data) > 0

JSON列の値を更新する

JSON_MODIFY関数を使用して、あるパスの値を更新できます。この関数を使用して、UPDATE文でJSONセルの元の値を変更できます。

update Product
set Data = JSON_MODIFY(Data, '$.Price', 24.99)
where ProductID = 17;

JSON_MODIFY関数は、価格キーを更新または作成します（存在しない場合）。新しい値がNULLの場合、キーは削除されます。 JSON_MODIFY関数は、新しい値を文字列として扱います（エスケープ特殊文字、適切なJSON文字列を作成するには二重引用符で囲みます）。新しい値がJSONフラグメントの場合は、JSON_QUERY関数でラップする必要があります。

update Product
set Data = JSON_MODIFY(Data, '$.tags', JSON_QUERY('["promo","new"]'))
where ProductID = 17;

2番目のパラメータのないJSON_QUERY関数は、「JSONへのキャスト」のように動作します。 JSON_QUERYの結果は有効なJSONフラグメント（オブジェクトまたは配列）なので、JSON_MODIFYは入力JSONを変更するときにこの値をエスケープしません。

JSON配列に新しい値を追加する

JSON_MODIFY関数を使用すると、JSON内のいくつかの配列に新しい値を追加できます。

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', "sales")
where ProductID = 17;

配列の最後に新しい値が追加されるか、["sales"]という値を持つ新しい配列が作成されます。 JSON_MODIFY関数は、新しい値を文字列として扱います（エスケープ特殊文字、適切なJSON文字列を作成するには二重引用符で囲みます）。新しい値がJSONフラグメントの場合は、JSON_QUERY関数でラップする必要があります。

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', JSON_QUERY('{"type":"new"}'))
where ProductID = 17;

内部JSONコレクションを持つJOINテーブル

JSONコレクションとしてフォーマットされ、JSON列として行内に格納された「子テーブル」がある場合は、このコレクションを展開してテーブルに変換し、親行と結合できます。標準JOIN演算子の代わりに、CROSS APPLYを使用する必要があります。この例では、製品部品はJSONオブジェクトのコレクションとしてフォーマットされ、Data列に格納されます。

select ProductID, Name, Size, Price, Quantity, PartName, Code
from Product
    CROSS APPLY OPENJSON(Data, '$.Parts') WITH (PartName varchar(20), Code varchar(5))

クエリの結果は、ProductテーブルとPartテーブル間の結合と等価です。

JSON配列のvalueを含む行を検索する

この例では、タグ配列には["promo"、 "sales"]のようなさまざまなキーワードが含まれているので、この配列とフィルタ値を開くことができます：

select ProductID, Name, Color, Size, Price, Quantity
from Product
    CROSS APPLY OPENJSON(Data, '$.Tags') 
where value = 'sales'

OPENJSONはタグの内部コレクションを開いてテーブルとして返します。次に、結果をテーブルの値でフィルタリングできます。

Modified text is an extract of the original Stack Overflow Documentation

ライセンスを受けた CC BY-SA 3.0

所属していない Stack Overflow

Microsoft SQL Server
JSONデータを使用したクエリ

サーチ…