TSQL を使用してデータをクエリし、JSON データを更新する

JSON はデータ交換用の非常に人気のあるデータ形式で、主に Web アプリケーションやモバイル アプリケーションで使用されます。 JSON はキーと値のペアを使用してデータを保存し、ネストされたキーと値のペアと配列という 2 つの複雑なデータ型を表します。指定された属性またはメンバーは使いやすく強力です。 JSON 形式は SQL Server 2016 バージョンでサポートされており、Unicode 文字型を使用して JSON データを表現し、JSON データを検証、クエリ、および変更できます。 JSON の検証と書式設定のためのツール、json formatter を推奨します。

1. JSON データの定義と検証

nvarchar を使用して JSON データを表し、関数 ISJSON を通じて JSON データが有効かどうかを検証します。

declare @json nvarchar(max)
set @json = 
N'{
 "info":{ 
  "type":1,
  "address":{ 
  "town":"bristol",
  "county":"avon",
  "country":"england"
  },
  "tags":["sport", "water polo"]
 },
 "type":"basic"
}'
 
select isjson(@json)

ISJSON 関数の形式は次のとおりです: ISJSON (式)、文字列が JSON データであることを示す 1 を返し、文字列が JSON データではないことを示す 0 を返します。次に、JSON データ式

Path 式の PATH は、Path Mode と Path の 2 つの部分に分かれています。パス モードはオプションで、lax と strict の 2 つのモードがあります。

1、パス モード

Path 式の先頭で、lax または strict キーワードを使用してパス モードを明示的に宣言できます。宣言されていない場合、デフォルトのパス モードは lax です。 lax モードでは、パス式にエラーがある場合、JSON 関数は NULL を返します。厳密モードでは、Path 式にエラーがある場合、JSON 関数はエラーをスローします。

2、Path 式

Path は、JSON データにアクセスする方法です。

$: 全体を表します。 JSON データ コンテンツ;

Comma.: フィールドまたはキーとも呼ばれる JSON オブジェクトのメンバーを表します。

ブラケット []: 要素の開始位置は 0 です。 : キーの名前。キー名を通じて対応する値を参照します。キー名にスペース、$、カンマ、括弧が含まれる場合は、二重引用符を使用します。たとえば、次の JSON データはパスを通じて JSON にルーティングできます。式の各属性:

{ "people": 
 [ 
 { "name": "John", "surname": "Doe" }, 
 { "name": "Jane", "surname": null, "active": true } 
 ] 
}

パス式によってクエリされるデータは次のとおりです:

$: JSON のコンテンツを表します。これは、最も外側の中括弧内のすべての項目です。この例は、people 配列と、その添え字です。配列は 0 から始まります。 ;

$.people[0]: people 配列の最初の要素を表します: { "name": "Jane", "surname": null, "active": true }

$.people [0].name : people 配列の最初の要素から、キーが Name である項目に対応するデータをクエリします。この場合は John です。

$.people[1].surname: に姓フィールドがあります。 people 配列の中央、Path 式は Path モードを宣言していないため、デフォルト値は緩いです。Path 式でエラーが発生すると、NULL が返されます。

3、Path を通じて JSON データをクエリします。

1、クエリを実行します。スカラー値 (JSON_VALUE)

JSON_VALUE(expression, path) 関数を使用して、JSON データから、Path パラメーターに従ってスカラー値を返します。返されるデータは文字型です。

declare @json nvarchar(max)
set @json = 
N'{
 "info":{ 
  "type":1,
  "address":{ 
  "town":"bristol",
  "county":"avon",
  "country":"england"
  },
  "tags":["sport", "water polo"]
 },
 "type":"basic"
}'
 
select
 json_value(@json, '$.type') as type,
 json_value(@json, '$.info.type') as info_type,
 json_value(@json, '$.info.address.town') as town,
 json_value(@json, '$.info.tags[0]') as tag

2. JSON データ (JSON_QUERY) を返します

JSON_QUERY (式 [ , path ]) 関数を使用して、Path パラメーターに従って JSON データ (JSON フラグメント) を返します (オプション)。 if オプション パラメーターが指定されていない場合、デフォルトのパスは $ です。つまり、JSON データ全体が返されます。

declare @json nvarchar(max)
set @json = 
N'{
 "info":{ 
  "type":1,
  "address":{ 
  "town":"bristol",
  "county":"avon",
  "country":"england"
  },
  "tags":["sport", "water polo"]
 },
 "type":"basic"
}'
 
select
 json_query(@json, '$') as json_context,
 json_query(@json, '$.info') as info,
 json_query(@json, '$.info.address') as info_address,
 json_query(@json, '$.info.tags') as info_tags

4.Pathを介してJSONデータを変更する

JSON_MODIFY(expression, path, newValue)を使用してJSONデータの属性値を変更し、変更されたJSONデータを返すプロセス。この関数は次のとおりです:

既存の属性を変更する: パラメーター パスに従って JSON データから指定された属性を検索し、属性の値をパラメーター newValue に変更します。戻り値は変更された JSON データです。新しいキーと値のペア (キー : 値のペア): 指定された属性が JSON データに存在しない場合は、パラメーター Path に従って、指定されたパスに新しいキーと値のペアを追加します。

キーと値のペアを削除します。 (キー: 値のペア): パラメーター newValue の値が NULL の場合、指定された属性を JSON データから削除することを意味します。

append キーワード: JSON 配列から要素を追加するために使用されます。データ要素を更新、挿入、削除し、JSON データに追加します

declare @info nvarchar(100) = '{"name":"john","skills":["c#","sql"]}'
-- update name
set @info = json_modify(@info, '$.name', 'mike')
-- insert surname
set @info = json_modify(@info, '$.surname', 'smith')
-- delete name
set @info = json_modify(@info, '$.name', null)
-- add skill
set @info = json_modify(@info, 'append $.skills', 'azure')

5 番目に、JSON データをリレーショナル テーブルに変換します

OPENJSON 関数は行セット関数 (RowSet) であり、JSON データをリレーショナル テーブル、

OPENJSON( jsonExpression [ , path ] )
[
 WITH (
  colName type [ column_path ] [ AS JSON ]
 [ , colName type [ column_path ] [ AS JSON ] ]
 [ , . . . n ]
  )
]

path パラメーター: テーブル パスとも呼ばれ、JSON データ内のリレーショナル テーブルのパスを指定します。

column_path パラメーター: path パラメーターに基づいて、リレーショナル テーブル JSON、列パスは常に明示的に指定する必要があります。

AS JSON 属性: AS JSON 属性が指定されている場合は、列のデータ型を nvarchar(max) として定義する必要があります。これは、列の値を意味します。 JSON データです。AS JSON 属性が指定されていない場合、列の値はスカラー値になります。

with 选项：指定关系表的Schema，应总是指定with选项；如果不指定with 选项，那么函数返回key，value和type三列；

示例，从JSON数据中，以关系表方式呈现数据

declare @json nvarchar(max)
set @json =
N'{
 "info":{
  "type":1,
  "address":{
  "town":"bristol",
  "county":"avon",
  "country":"england"
  },
  "tags":["sport", "water polo"]
 },
 "type":"basic"
}'
 
SELECT info_type,info_address,tags
FROM OPENJSON(@json, '$.info')
with
(
info_type tinyint 'lax $.type',
info_address nvarchar(max) 'lax $.address' as json,
tags nvarchar(max) 'lax $.tags' as json
)

   

六，将关系表数据以JSON格式存储

通过For JSON  Auto/Path，将关系表数据存储为JSON格式，

Auto 模式：根据select语句中column的顺序，自动生成JSON数据的格式；

Path 模式：使用column name的格式来生成JSON数据的格式，column name使用逗号分隔（dot-separated）表示组-成员关系；

1，以Auto 模式生成JSON格式

select id,
 name,
 category
from dbo.dt_json
for json auto,root('json')

   

返回的数据格式是

{
 "json":[
  {
   "id":1,
   "name":"C#",
   "category":"Computer"
  },
  {
   "id":2,
   "name":"English",
   "category":"Language"
  },
  {
   "id":3,
   "name":"MSDN",
   "category":"Web"
  },
  {
   "id":4,
   "name":"Blog",
   "category":"Forum"
  }
 ]
}

   

2，以Path模式生成JSON格式

select id as 'book.id',
 name as 'book.name',
 category as 'product.category'
from dbo.dt_json
for json path,root('json')

   

返回的数据格式是：

{
"json":[
{
"book":{
"id":1,
"name":"C#"
},
"product":{
"category":"Computer"
}
},
{
"book":{
"id":2,
"name":"English"
},
"product":{
"category":"Language"
}
},
{
"book":{
"id":3,
"name":"MSDN"
},
"product":{
"category":"Web"
}
},
{
"book":{
"id":4,
"name":"Blog"
},
"product":{
"category":"Forum"
}
}
]
}

   

以上就是使用TSQL查询数据和更新JSON数据的内容，更多相关内容请关注PHP中文网（www.php.cn）！