SQL Server 2016でJSONを使用する

JSONは現在、開発で最も使用されているデータ形式の1つです。 最新のサービスのほとんどは、JSONの形式で情報を返します。 JSONは、たとえば、構造化された情報をファイルに保存するための推奨形式でもあります。 多くのデータがJSON形式で使用されるため、SQL ServerでのJSONサポートは、他のサービスとデータを交換できるようにするために特に重要になります。



JSONは、SQL Server 2016に追加された最も人気のある機能の1つになりました。この記事では、JSONを操作するための基本的なメカニズムについて検討します。

短いレビュー

SQL ServerでJSONを操作する関数を使用すると、JSONデータを分析およびクエリし、JSONをリレーショナルビューに変換し、SQLクエリの結果をJSONとしてエクスポートできます。







JSONがある場合は、そこからデータを取得するか、組み込み関数JSON_VALUE、JSON_QUERY、およびISJSONを使用して有効性を確認できます。 JSON_MODIFY関数を使用して、データを変更できます。 より高度な使用のために、OPENJSON関数を使用すると、JSONオブジェクトの配列を一連の文字列に変換できます。 次に、このセットに対して任意のSQLクエリを実行できます。 最後に、リクエストの結果をJSONに変換するFOR JSONコンストラクトがあります。



いくつかの簡単な例を見てみましょう。 次のコードでは、JSONが含まれるテキスト変数を定義します。

DECLARE @json NVARCHAR(4000) SET @json = N'{ "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }, "type":"Basic" }'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これで、JSON_VALUEおよびJSON_QUERYを使用してJSONから個々の値またはオブジェクトを取得できます。

 SELECT JSON_VALUE(@json, '$.type') as type, JSON_VALUE(@json, '$.info.address.town') as town, JSON_QUERY(@json, '$.info.tags') as tags
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このクエリは、「Basic」、「Bristol」、[「Sport」、「Water polo」]を返します。 JSON_VALUE関数は、JSONからスカラー値（つまり、ストリング、数値、ブール値）を返します。これは、2番目のパラメーターで指定された「パス」に沿って配置されます。 JSON_QUERYは、オブジェクトまたは配列（この例ではタグの配列）を「パス」に沿って返します。 組み込みのJSON関数はJavaScriptのような構文を使用して、2番目のパラメーターとして値とオブジェクトにアクセスします。



OPENJSON関数を使用すると、JSON内の配列にアクセスし、この配列の要素を返すことができます。

 SELECT value FROM OPENJSON(@json, '$.info.tags')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この例では、タグの配列から文字列値を返します。 さらに、OPENJSONは任意の複雑なオブジェクトを返すことができます。



最後に、FOR JSONコンストラクトは、JSONでSQLクエリを実行した結果をフォーマットできます。

 SELECT object_id, name FROM sys.tables FOR JSON PATH
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これらの関数をさらに詳しく考えてみましょう。

SQL ServerのJSONデータストレージ

SQL Serverでは、JSONはテキストとして保存されます。 これにはNVARCHAR型を使用できます。 次の例では、InfoJsonフィールドにJSONを保存します。

 CREATE TABLE Person ( Id int IDENTITY PRIMARY KEY NONCLUSTERED, FirstName nvarchar(100) NOT NULL, LastName nvarchar(100) NOT NULL, InfoJson nvarchar(max) ) WITH (MEMORY_OPTIMIZED=ON)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

SQL Server 2016では、1つのテーブルで通常の列（例のFirstNameとLastName）とJSONの列（例のInfoJSON）を組み合わせることができます。 JSON列を空間列およびXMLと組み合わせることもできます。 リレーショナルまたはドキュメントベースのストレージのみとは異なり、ストレージの原則を選択して開発の柔軟性を高めることができます。



JSONはテキスト列に格納されますが、単なるテキストではありません。 SQL Serverには、サイズを最大50％節約するUNICODE圧縮などのさまざまな圧縮メカニズムを使用して、テキスト列のストレージを最適化するメカニズムがあります。 JSONを列ストアインデックス付きのテーブルに保存するか、GZipアルゴリズムを使用する組み込みのCOMPRESS関数を使用して明示的に圧縮することもできます。



JSONは、NVARCHAR型で動作するSQL Serverのコンポーネントと完全に互換性があります。 上記の例では、JSONはメモリ内のOLTP（ヘカトン）テーブルに格納されており、これによりパフォーマンスが向上しています。 JSONを通常のテーブルに保存し、カラムストアインデックスまたはFILESTREAMを使用できます。 Polybaseテーブルを使用してHadoopからダウンロードしたり、ファイルシステムから読み取ったり、Azure SQLで作業したり、レプリケーションを使用したりすることもできます。 JSONを格納するテーブルを、一時テーブルセキュリティや行レベルセキュリティなどの他のSQL Server機能と組み合わせると、既存のドキュメント指向DBMSにはない強力な機能が見つかる場合があります。



保存されたJSONの有効性を確認する場合は、ISJSON制約と関数を使用して検証チェックを追加できます。

 ALTER TABLE Person ADD CONSTRAINT [Content should be formatted as JSON] CHECK ( ISJSON( InfoJSON )> 0 )
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

実行時に、JSONの形式が正しくない場合、リクエストは機能しません。



なぜなら JSONはテキスト形式で表示されるため、アプリケーションで何かを変更する必要はありません。 JSONを通常の文字列として使用できます。 JSONは、ORMを文字列として使用してロードし、クライアント側のJavaScriptアプリケーションに送信できます。 すべてのデータ抽出ユーティリティも機能します。

JSON処理の組み込み関数

SQL Server 2016は、JSONを処理するためのいくつかの機能を提供します。

• ISJSON （jsonText）は、仕様に対してJSONの有効性をチェックします。 この関数を使用すると、JSONを含む列に制限を課すことができます
• JSON_VALUE （jsonText、path）はjsonTextを解析し、特定の「パス」によって個々の値を選択します（以下の例を参照）
• JSON_QUERY （jsonText、path）はjsonTextを解析し、特定の「パス」に従ってオブジェクトまたは配列を選択します（以下の例を参照）
• JSON_MODIFY （jsonText、path、newValue）は、特定の「パス」のプロパティの値を変更します（以下の例を参照）

これらの関数は、JSONの「パス」を使用して値またはオブジェクトにアクセスします。 例：

 '$' //     JSON    '$.property1' //   property1   JSON '$[4]' //   5-    (   0) '$.property1.property2.array1[5].property3.array2[15].property4' //     '$.info."first name"' //    "first name"  .      (,    ..),       
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

JSON_MODIFY関数を使用する場合、追加の修飾子をpathパラメーターで使用できます。 一般に、「パス」の構文は次のようになります。

  [追加] [緩い|  strict] $ .json_path 

append修飾子を指定すると、json_path参照配列に新しい値が追加されます。 lax修飾子は、プロパティが存在するかどうかに関係なく動作モードを設定します。 そうでない場合は、追加されます。 strictを使用する場合、プロパティがないとエラーが生成されます。



ドル記号（$）は、JSONオブジェクト全体を指します（XPathのルートノード「/」に似ています）。 「$」の後に任意のプロパティを追加して、オブジェクトの要素を参照できます。 簡単な例を考えてみましょう。

 SELECT Id, FirstName, LastName, JSON_VALUE(InfoJSON, '$.info."social security number"') as SSN, JSON_QUERY(InfoJSON, '$.skills') as Skills FROM Person AS t WHERE ISJSON( InfoJSON ) > 0 AND JSON_VALUE(InfoJSON, '$.Type') = 'Student'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このクエリは、通常の列から名と姓、JSON列からソーシャル番号とスキル配列を返します。 結果は、InfoJSON列に有効なJSONが含まれている必要があり、JSON列のType値が「Student」であるという条件によってフィルター処理されます。 既に理解したように、リクエストの任意の部分（ソート、グループ化など）でJSONの値を使用できます。

JSONをリレーショナルビューに変換する-OPENJSON

OPENJSON関数は、オブジェクトの配列を定義するテーブルを返し、配列を反復処理して、配列の各要素を行に表示します。

例

入力データ（JSON）：

 { "Orders": [ { "Order": { "Number": "S043659", "Date": "2011-05-31T00:00:00" }, "Account": "Microsoft", "Item": { "Price": 59.99, "Quantity": 1 } }, { "Order": { "Number": "S043661", "Date": "2011-06-01T00:00:00" }, "Account": "Nokia", "Item": { "Price": 24.99, "Quantity": 3 } } ] }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

SQLクエリ：

 SELECT * FROM OPENJSON(@json, N'$.Orders') WITH ( Number VARCHAR(200) N'$.Order.Number', Date DATETIME N'$.Order.Date', Customer VARCHAR(200) N'$.Account', Quantity INT N'$.Item.Quantity' )
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果

 | 番号| 日付| お客様| 数量
 -------------------------------------------------- --------
 |  S043659 |  2011-05-31 00：00：00.000 | マイクロソフト|  1
 |  S043661 |  2011-06-01 00：00：00.000 | ノキア|  3

上記の例では、処理中のJSON配列を検索する場所（つまり、$ .Ordersパスに沿って）、返す列、およびJSONオブジェクトのどこにセルとして返す値があるかを決定しました。



OPENJSONは、データを操作するときのクエリで使用できます。 例のように、orders変数のJSON配列を一連の行に変換し、それらを通常のテーブルに貼り付けることができます。

 INSERT INTO Orders(Number, Date, Customer, Quantity) SELECT Number, Date, Customer, Quantity OPENJSON (@orders) WITH ( Number varchar(200), Date datetime, Customer varchar(200), Quantity int ) AS OrdersArray
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

返されるOPENJSONデータセットの4列は、WITH句を使用して定義されます。 OPENJSONは、各JSONオブジェクトでNumber、Date、Customer、Quantityの各プロパティを検索し、結果のデータセットの値を列に変換しようとします。 デフォルトでは、プロパティが見つからない場合、NULLが返されます。 次のJSONがorders変数に含まれていると仮定します。

 '[ {"Number":1, "Date": "8/10/2012", "Customer": "Adventure works", "Quantity": 1200}, {"Number":4, "Date": "5/11/2012", "Customer": "Adventure works", "Quantity": 100}, {"Number":6, "Date": "1/3/2012", "Customer": "Adventure works", "Quantity": 250}, {"Number":8, "Date": "12/7/2012", "Customer": "Adventure works", "Quantity": 2200} ]'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ご覧のとおり、JSONからリレーショナル形式への変換は非常に簡単です。 必要なのは、列の名前と型を決定することだけです。OPENJSONは、列に対応するJSONのプロパティを検索します。 この例では、シンプルなシングルレベルJSONを使用していますが、OPENJSONは複雑なネストされたオブジェクトを処理できます。



OPENJSONは、同じクエリでリレーショナルデータとJSONを組み合わせる必要がある場合にも使用できます。 前の例のJSON配列がOrdersJson列に格納されているとします。 次のクエリは、通常のフィールドとJSONフィールドを返します。

 SELECT Id, FirstName, LastName, Number, Date, Customer, Quantity FROM Person CROSS APPLY OPENJSON (OrdersJson) WITH ( Number varchar(200), Date datetime, Customer varchar(200), Quantity int ) AS OrdersArray
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

OPENJSONは各セルの配列を処理し、配列内のJSONオブジェクトごとに1行を返します。 CROSS APPLY OPENJSON構文は、テーブルの行とJSONデータを組み合わせるために使用されます。

JSONデータのインデックス作成

JSONの値はテキストとして保存されますが、列の通常の値としてインデックスを作成できます。 非クラスター化インデックスまたはフルテキストインデックスを使用できます。



クエリでよく使用されるJSONプロパティにインデックスを作成する必要がある場合、目的のプロパティを参照する計算列を作成してから、このフィールドに通常のインデックスを作成できます。 次の例では、InfoJSON列の$ .Companyプロパティを使用して行をフィルタリングするクエリを最適化します。

 ALTER TABLE Person ADD vCompany AS JSON_VALUE(InfoJSON, '$.Company') CREATE INDEX idx_Person_1 ON Person(vCompany)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

SQL Serverには、JSONの通常の列と値を単一のインデックスに結合できるハイブリッドモデルが用意されています。



なぜなら JSONは単なるテキストであり、フルテキストインデックスを使用できます。 値の配列にフルテキストインデックスを作成できます。 JSON配列を含む列にフルテキストインデックスを作成するか、配列を参照する計算列を作成して、その列にフルテキストインデックスを追加できます。

 ALTER TABLE Person ADD vEmailAddresses AS JSON_QUERY(InfoJSON, '$.Contact.Emails') CREATE FULLTEXT INDEX ON Person(vEmailAddresses) KEY INDEX PK_Person_ID ON jsonFullTextCatalog;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

フルテキストインデックスは、JSON配列の値を検索するクエリを最適化する必要がある場合に便利です。

 SELECT PersonID, FirstName,LastName,vEmailAddresses FROM Person WHERE CONTAINS(vEmailAddresses, 'john@mail.microsoft.com')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このクエリは、電子メールアドレスの配列に「john@mail.microsoft.com」が含まれるPersonから文字列を返します。 フルテキストインデックスには、特別なJSON解析ルールはありません。 区切り文字（二重引用符、コンマ、角括弧）を使用して配列を分割し、配列内の値にインデックスを付けます。 フルテキストインデックスは、数値または文字列の配列に適用されます。 JSONにもっと複雑なオブジェクトがある場合、フルテキストインデックスはキーと値を区別できないため、適用できません。



一般に、インデックスを作成するための同じ原則を通常の列またはJSON列に適用できます。

JSONへのデータのエクスポート-FOR JSON

SQL Serverには、FOR JSONコンストラクトを使用してリレーショナルデータをJSONに変換する機能があります。 FOR XML構造に精通している場合は、FOR JSONをすでに実際に知っています。

例

ソースデータ

 | 番号| 日付| お客様| 価格| 数量|
 -------------------------------------------------- ------------------
 |  S043659 |  2011-05-31 00：00：00.000 | マイクロソフト|  59.99 |  1 |
 |  S043661 |  2011-06-01 00：00：00.000 | ノキア|  24.99 |  3 |

SQLクエリ

 SELECT Number AS [Order.Number], Date AS [Order.Date], Customer AS [Account], Price AS 'Item.UnitPrice', Quantity AS 'Item.Qty' FROM SalesOrder FOR JSON PATH, ROOT('Orders')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果のJSON

 { "Orders": [ { "Order": { "Number": "S043659", "Date": "2011-05-31T00:00:00" }, "Account": "Microsoft", "Item": { "UnitPrice": 59.99, "Qty": 1 } }, { "Order": { "Number": "S043661", "Date": "2011-06-01T00:00:00" }, "Account": "Nokia", "Item": { "UnitPrice": 24.99, "Qty": 3 } } ] }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

SELECTクエリの最後にFOR JSONを追加すると、SQL Serverは結果をJSONとしてフォーマットします。 各行は単一のJSONオブジェクトとして表され、セルの値はJSON値になり、列名はキーとして使用されます。 FOR JSONコンストラクトには2つのタイプがあります。

• FOR JSON PATHを使用すると、列名を使用して出力でJSON構造を定義できます。 列の同義語としてドット区切りの名前を使用する場合、JSONプロパティは命名規則に従います。 これは、スラッシュで区切られたパスを指定できるFOR XML PATHに似ています。
  
  
  
  
• FOR JSON AUTOは、クエリ内のテーブルの階層に基づいてネストされた配列を自動的に作成します。 FOR XML AUTOに似ています。

おわりに

SQL ServerのJSON関数を使用すると、JSONの形式でデータをクエリおよび分析したり、リレーショナルビューに変換したり、その逆に変換したりできます。 これにより、追加の変換なしでJSONを送受信する外部システムにSQL Serverを統合できます。



SQL Serverは、リレーショナルデータとJSONデータを1つのテーブルに組み合わせた場合のハイブリッドストレージモデルも提供します。 このモデルは、高速のデータアクセス速度と柔軟なアプリケーション開発機能の妥協点を提供します。



さらに、JSONの値に通常の列としてインデックスを付けたり、FOR JSONを使用してリレーショナルデータをJSONに変換したり、OPENJSONを使用してその逆を行ったりすることができます。