Fragen Sie Daten ab und aktualisieren Sie JSON-Daten mit TSQL

JSON ist ein sehr beliebtes Datenformat für den Datenaustausch, das hauptsächlich in Web- und Mobilanwendungen verwendet wird. JSON verwendet Schlüssel/Wert-Paare zum Speichern von Daten und stellt zwei komplexe Datentypen dar: verschachtelte Schlüssel-Wert-Paare und Arrays. Es kann an die angegebenen Attribute oder Mitglieder weitergeleitet werden, die einfach zu verwenden und leistungsstark sind. Die SQL Server 2016-Version unterstützt das JSON-Format, verwendet Unicode-Zeichentypen zur Darstellung von JSON-Daten und kann JSON-Daten überprüfen, abfragen und ändern. Empfehlen Sie ein Tool zur JSON-Überprüfung und -Formatierung: JSON Formatter.

1. Definieren und überprüfen Sie JSON-Daten

Verwenden Sie nvarchar, um JSON-Daten darzustellen, und überprüfen Sie mithilfe der Funktion ISJSON, ob die JSON-Daten gültig sind.

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)

Das Format der ISJSON-Funktion ist: ISJSON (Ausdruck), gibt 1 zurück, was angibt, dass es sich bei der Zeichenfolge um JSON-Daten handelt; gibt 0 zurück, was angibt, dass es sich bei der Zeichenfolge nicht um JSON-Daten handelt; Ausdruck ist NULL;

2. PATH-Ausdruck von JSON-Daten

Der Pfadausdruck ist in zwei Teile unterteilt: Pfadmodus und Pfad. Der Pfadmodus ist optional und verfügt über zwei Modi: lax und streng.

1, Pfadmodus

Am Anfang des Pfadausdrucks können Sie den Pfadmodus explizit über die Schlüsselwörter lax oder strict deklarieren. Wenn nicht deklariert, ist der Standardpfadmodus lax. Wenn im laxen Modus ein Fehler im Pfadausdruck vorliegt, gibt die JSON-Funktion NULL zurück. Wenn im strikten Modus ein Fehler im Pfadausdruck vorliegt, gibt die JSON-Funktion einen Fehler aus

2, Pfadausdruck

Pfad ist die Möglichkeit, auf JSON-Daten zuzugreifen, und es gibt vier Operatoren:

$: stellt den Inhalt der gesamten JSON-Daten dar;

Komma: stellt die Mitglieder des JSON-Objekts dar, auch Felder (Field) oder Schlüssel genannt; 🎜>eckige Klammern []: Gibt die Elemente im Array an, die Startposition der Elemente ist 0;

Schlüsselname: Der Name des Schlüssels, siehe den entsprechenden Wert über den Schlüsselnamen; Der Name enthält Leerzeichen, $, Kommas und Klammern. Verwenden Sie doppelte Anführungszeichen.

Zum Beispiel gibt es die folgenden JSON-Daten, die über den Pfadausdruck an jedes Attribut von JSON weitergeleitet werden können:

Die vom Path-Ausdruck abgefragten Daten sind:

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

$: Stellt den Inhalt von JSON dar, also alle Elemente in den äußersten geschweiften Klammern. In diesem Beispiel handelt es sich um ein Personenarray und den Index des Arrays beginnt bei 0;

$ people[0]: Stellt das erste Element des People-Arrays dar: { „name“: „Jane“, „surname“: null, „active“: true }

$.people[0].name: From Fragen Sie im ersten Element des People-Arrays die Daten ab, die dem Element entsprechen, dessen Schlüssel Name ist, in diesem Fall

$. people[1].surname: In der Mitte des People-Arrays befindet sich ein Nachnamenfeld, da der Path-Ausdruck in der Formel den Path-Modus nicht deklariert und der Standardwert lax ist, wenn im Path-Ausdruck ein Fehler auftritt zurückgegeben;

3. JSON-Daten über Pfad

1 abfragen, Skalarwert (JSON_VALUE) abfragen

Verwenden Sie die Funktion JSON_VALUE(Ausdruck, Pfad), um einen Skalarwert zurückzugeben aus JSON-Daten gemäß dem Path-Parameter. Die zurückgegebenen Daten sind ein Zeichentyp.

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-Daten zurückgeben (JSON_QUERY)

Verwenden Sie die Funktion JSON_QUERY (Ausdruck [, Pfad]), um JSON-Daten entsprechend zurückzugeben Pfadparameter. (JSON-Fragment); der Parameterpfad ist optional, der Standardpfad ist $, d. h. die gesamten JSON-Daten werden zurückgegeben.

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. Ändern Sie JSON-Daten über den Pfad

Verwenden Sie JSON_MODIFY (Ausdruck, Pfad, neuer Wert), um die Attributwerte zu ändern in den JSON-Daten und gibt die geänderten JSON-Daten zurück. Der Prozess dieser Funktion zum Ändern von JSON-Daten ist:

Vorhandene Attribute ändern: Suchen Sie das angegebene Attribut aus den JSON-Daten entsprechend dem Parameterpfad und ändern Sie den Wert des Attributs zum Parameter newValue, der Rückgabewert sind die geänderten JSON-Daten.

Fügen Sie ein neues Schlüssel-Wert-Paar hinzu (Schlüssel:Wert-Paar): Wenn das angegebene Attribut nicht in den JSON-Daten vorhanden ist, dann Fügen Sie gemäß dem Parameter Path im angegebenen Pfad ein neues Schlüssel-Wert-Paar hinzu angegebenes Attribut aus den JSON-Daten;

append Schlüsselwort: wird verwendet, um ein Element aus einem JSON-Array anzuhängen

Beispiel: Aktualisieren, Einfügen, Löschen und Anhängen von Datenelementen an JSON-Daten

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-Daten in relationale Tabelle konvertieren

OPENJSON-Funktion ist eine Rowset-Funktion (RowSet), die konvertieren kann JSON-Daten in relationale Tabelle,

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

Pfadparameter: Auch Tabellenpfad genannt, gibt den Pfad der relationalen Tabelle in den JSON-Daten an;

column_path-Parameter: Geben Sie basierend auf dem Pfadparameter den Pfad jeder Spalte in der relationalen Tabelle JSON an. Der Spaltenpfad sollte immer explizit angegeben werden.

AS JSON-Attribut: Wenn das AS JSON-Attribut angegeben ist, muss der Datentyp der Spalte als nvarchar(max) definiert sein. Dies bedeutet, dass es sich bei dem Wert der Spalte um JSON-Daten handelt. Wenn das AS-JSON-Attribut nicht angegeben ist, ist der Wert der Spalte ein Skalarwert 🎜>

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）！