Основы работы с JSON в SQL Server.

JSON — краткая предыстория

JSON — это аббревиатура от JavaScript Object Notation, которая стала популярной чуть более семнадцати лет назад. JSON — это по сути формат данных, его популяризировал Дуглас Крокфорд, известный программист с интересной историей, который также участвовал в разработке JavaScript. JSON почти заменил XML в качестве кроссплатформенного формата обмена данными. Сообщается, что он легкий и им легче манипулировать по сравнению с XML. В AWS CloudFormation шаблоны, которые на самом деле представляют собой документы в формате JSON (или YAML), используются для описания ресурсов AWS при автоматизации развертывания.

JSON также широко используется в базах данных NoSQL, таких как все более популярная MongoDB. Практически все гиганты социальных сетей предоставляют API, основанные на JSON. Я уверен, что вы начинаете понимать, насколько широко стали применяться его приложения. JSON был стандартизирован в 2013 году, а последняя версия стандарта (ECMA-404: синтаксис обмена данными JSON) была выпущена в 2017 году.

SQL Server представил поддержку JSON в SQL Server 2016.

Формат JSON

Документы JSON представлены в виде серии объектов JSON, содержащих пары "имя-значение". Объекты JSON могут усложняться, поскольку мы вводим компоненты, которые сами по себе являются не просто значениями, а массивами. Ниже показан формат документа JSON на основе стандарта EMCA-404:

-- Listing 1: Sample JSON Document
[
{
"empid":1,
"lastname":"Davis",
"firstname":"Sara",
"title":"CEO",
"titleofcourtesy":"Ms.",
"birthdate":"1968-12-08",
"hiredate":"2013-05-01",
"address":"7890 - 20th Ave. E., Apt. 2A",
"city":"Seattle",
"region":"WA",
"postalcode":"10003",
"country":"USA",
"phone":"(206) 555-0101"
},
{
"empid":2,
"lastname":"Funk",
"firstname":"Don",
"title":"Vice President, Sales",
"titleofcourtesy":"Dr.",
"birthdate":"1972-02-19",
"hiredate":"2013-08-14",
"address":"9012 W. Capital Way",
"city":"Tacoma",
"region":"WA",
"postalcode":"10001",
"country":"USA",
"phone":"(206) 555-0100",
"mgrid":1
},
{
"empid":3,
"lastname":"Lew",
"firstname":"Judy",
"title":"Sales Manager",
"titleofcourtesy":"Ms.",
"birthdate":"1983-08-30",
"hiredate":"2013-04-01",
"address":"2345 Moss Bay Blvd.",
"city":"Kirkland",
"region":"WA",
"postalcode":"10007",
"country":"USA",
"phone":"(206) 555-0103",
"mgrid":2
},
{
"empid":4,
"lastname":"Peled",
"firstname":"Yael",
"title":"Sales Representative",
"titleofcourtesy":"Mrs.",
"birthdate":"1957-09-19",
"hiredate":"2014-05-03",
"address":"5678 Old Redmond Rd.",
"city":"Redmond",
"region":"WA",
"postalcode":"10009",
"country":"USA",
"phone":"(206) 555-0104",
"mgrid":3
},
{
"empid":5,
"lastname":"Mortensen",
"firstname":"Sven",
"title":"Sales Manager",
"titleofcourtesy":"Mr.",
"birthdate":"1975-03-04",
"hiredate":"2014-10-17",
"address":"8901 Garrett Hill",
"city":"London",
"postalcode":"10004",
"country":"UK",
"phone":"(71) 234-5678",
"mgrid":2
},
{
"empid":6,
"lastname":"Suurs",
"firstname":"Paul",
"title":"Sales Representative",
"titleofcourtesy":"Mr.",
"birthdate":"1983-07-02",
"hiredate":"2014-10-17",
"address":"3456 Coventry House, Miner Rd.",
"city":"London",
"postalcode":"10005",
"country":"UK",
"phone":"(71) 345-6789",
"mgrid":5
},
{
"empid":7,
"lastname":"King",
"firstname":"Russell",
"title":"Sales Representative",
"titleofcourtesy":"Mr.",
"birthdate":"1980-05-29",
"hiredate":"2015-01-02",
"address":"6789 Edgeham Hollow, Winchester Way",
"city":"London",
"postalcode":"10002",
"country":"UK",
"phone":"(71) 123-4567",
"mgrid":5
},
{
"empid":8,
"lastname":"Cameron",
"firstname":"Maria",
"title":"Sales Representative",
"titleofcourtesy":"Ms.",
"birthdate":"1978-01-09",
"hiredate":"2015-03-05",
"address":"4567 - 11th Ave. N.E.",
"city":"Seattle",
"region":"WA",
"postalcode":"10006",
"country":"USA",
"phone":"(206) 555-0102",
"mgrid":3
},
{
"empid":9,
"lastname":"Doyle",
"firstname":"Patricia",
"title":"Sales Representative",
"titleofcourtesy":"Ms.",
"birthdate":"1986-01-27",
"hiredate":"2015-11-15",
"address":"1234 Houndstooth Rd.",
"city":"London",
"postalcode":"10008",
"country":"UK",
"phone":"(71) 456-7890",
"mgrid":5
}
]

Документ в листинге 1 был извлечен из обычной таблицы базы данных SQL Server с помощью запроса из листинга 2. В листинге 2 показан ответ от SQL Server Management Studio после выполнения запроса: «Затронуто 9 строк». По сути, SQL Server преобразует каждую строку исходной таблицы в объект JSON. В каждом объекте имя столбца преобразуется в имя JSON, а значение для этого столбца в этой строке представляется как значение JSON.

-- Listing 2: Using the FOR JSON Clause
USE TSQLV4
GO
SELECT * FROM HR.Employees 
FOR JSON AUTO;
USE TSQLV4
GO
SELECT * FROM HR.Employees 
FOR JSON PATH;

JSON-функции SQL Server

В предыдущем разделе мы использовали предложение FOR JSON, предназначенное для форматирования результатов запроса в формате JSON. SQL Server, в свою очередь, предоставляет следующие функции для управления форматами JSON внутри SQL Server:

OPENJSON

OPENJSON можно использовать для преобразования данных в формате JSON в реляционный формат. В листинге 3 показан пример этого с использованием первого объекта в образце документа JSON, упомянутом в листинге 1. Подход включает в себя сначала определение строковой переменной @json и передачу нашего объекта JSON в качестве параметра этой переменной. Затем мы передаем переменную функции OPENJSON в операторе SELECT. Выполнение запроса создает набор результатов с тремя столбцами: ключ, значение и тип. JSON, в отличие от XML, имеет определения типов для каждого значения в документе. В этом случае мы видим, что представлены Тип 2 (числовые данные) и Тип 1 (строковые данные).

-- Listing 3 Using OPENJSON 
DECLARE @json NVARCHAR(4000) = N'{
"empid":1,
"lastname":"Davis",
"firstname":"Sara",
"title":"CEO",
"titleofcourtesy":"Ms.",
"birthdate":"1968-12-08",
"hiredate":"2013-05-01",
"address":"7890 - 20th Ave. E., Apt. 2A",
"city":"Seattle",
"region":"WA",
"postalcode":"10003",
"country":"USA",
"phone":"(206) 555-0101"
}';
SELECT * FROM OPENJSON (@json);

В листинге 4 мы используем тот же подход со всем текстом JSON, включая квадратные скобки [], что приводит к выводу, показанному на рис. 5. Обратите внимание на значение в столбце Type этого вывода (5), означающее значение, которое мы имеем в поле является объектом JSON. В таблице 1 показан список типов данных JSON.

-- Listing 4 Using OPENJSON
DECLARE @json NVARCHAR(4000) = N'
[{
"empid":1,
"lastname":"Davis",
"firstname":"Sara",
"title":"CEO",
"titleofcourtesy":"Ms.",
"birthdate":"1968-12-08",
"hiredate":"2013-05-01",
"address":"7890 - 20th Ave. E., Apt. 2A",
"city":"Seattle",
"region":"WA",
"postalcode":"10003",
"country":"USA",
"phone":"(206) 555-0101"
},
{
"empid":2,
"lastname":"Funk",
"firstname":"Don",
"title":"Vice President, Sales",
"titleofcourtesy":"Dr.",
"birthdate":"1972-02-19",
"hiredate":"2013-08-14",
"address":"9012 W. Capital Way",
"city":"Tacoma",
"region":"WA",
"postalcode":"10001",
"country":"USA",
"phone":"(206) 555-0100",
"mgrid":1
},
{
"empid":3,
"lastname":"Lew",
"firstname":"Judy",
"title":"Sales Manager",
"titleofcourtesy":"Ms.",
"birthdate":"1983-08-30",
"hiredate":"2013-04-01",
"address":"2345 Moss Bay Blvd.",
"city":"Kirkland",
"region":"WA",
"postalcode":"10007",
"country":"USA",
"phone":"(206) 555-0103",
"mgrid":2
},
{
"empid":4,
"lastname":"Peled",
"firstname":"Yael",
"title":"Sales Representative",
"titleofcourtesy":"Mrs.",
"birthdate":"1957-09-19",
"hiredate":"2014-05-03",
"address":"5678 Old Redmond Rd.",
"city":"Redmond",
"region":"WA",
"postalcode":"10009",
"country":"USA",
"phone":"(206) 555-0104",
"mgrid":3
},
{
"empid":5,
"lastname":"Mortensen",
"firstname":"Sven",
"title":"Sales Manager",
"titleofcourtesy":"Mr.",
"birthdate":"1975-03-04",
"hiredate":"2014-10-17",
"address":"8901 Garrett Hill",
"city":"London",
"postalcode":"10004",
"country":"UK",
"phone":"(71) 234-5678",
"mgrid":2
},
{
"empid":6,
"lastname":"Suurs",
"firstname":"Paul",
"title":"Sales Representative",
"titleofcourtesy":"Mr.",
"birthdate":"1983-07-02",
"hiredate":"2014-10-17",
"address":"3456 Coventry House, Miner Rd.",
"city":"London",
"postalcode":"10005",
"country":"UK",
"phone":"(71) 345-6789",
"mgrid":5
},
{
"empid":7,
"lastname":"King",
"firstname":"Russell",
"title":"Sales Representative",
"titleofcourtesy":"Mr.",
"birthdate":"1980-05-29",
"hiredate":"2015-01-02",
"address":"6789 Edgeham Hollow, Winchester Way",
"city":"London",
"postalcode":"10002",
"country":"UK",
"phone":"(71) 123-4567",
"mgrid":5
},
{
"empid":8,
"lastname":"Cameron",
"firstname":"Maria",
"title":"Sales Representative",
"titleofcourtesy":"Ms.",
"birthdate":"1978-01-09",
"hiredate":"2015-03-05",
"address":"4567 - 11th Ave. N.E.",
"city":"Seattle",
"region":"WA",
"postalcode":"10006",
"country":"USA",
"phone":"(206) 555-0102",
"mgrid":3
},
{
"empid":9,
"lastname":"Doyle",
"firstname":"Patricia",
"title":"Sales Representative",
"titleofcourtesy":"Ms.",
"birthdate":"1986-01-27",
"hiredate":"2015-11-15",
"address":"1234 Houndstooth Rd.",
"city":"London",
"postalcode":"10008",
"country":"UK",
"phone":"(71) 456-7890",
"mgrid":5
}]';
SELECT * FROM OPENJSON (@json);

Чтобы представить данные JSON в виде полной реляционной таблицы, мы начали с листинга 2, мы должны указать имена столбцов и типы данных, в которые мы преобразуем. Мы достигаем этого с помощью кода в листинге 5. Сравнивая вывод, который мы получаем, с выводом, когда мы запрашиваем таблицу HR.Employees напрямую, мы видим, что получаем точно такие же данные (см. рис. 6 и 7).

-- Listing 5 Using OPENJSON
DECLARE @json NVARCHAR(4000) = N'
[{
"empid":1,
"lastname":"Davis",
"firstname":"Sara",
"title":"CEO",
"titleofcourtesy":"Ms.",
"birthdate":"1968-12-08",
"hiredate":"2013-05-01",
"address":"7890 - 20th Ave. E., Apt. 2A",
"city":"Seattle",
"region":"WA",
"postalcode":"10003",
"country":"USA",
"phone":"(206) 555-0101"
},
{
"empid":2,
"lastname":"Funk",
"firstname":"Don",
"title":"Vice President, Sales",
"titleofcourtesy":"Dr.",
"birthdate":"1972-02-19",
"hiredate":"2013-08-14",
"address":"9012 W. Capital Way",
"city":"Tacoma",
"region":"WA",
"postalcode":"10001",
"country":"USA",
"phone":"(206) 555-0100",
"mgrid":1
},
{
"empid":3,
"lastname":"Lew",
"firstname":"Judy",
"title":"Sales Manager",
"titleofcourtesy":"Ms.",
"birthdate":"1983-08-30",
"hiredate":"2013-04-01",
"address":"2345 Moss Bay Blvd.",
"city":"Kirkland",
"region":"WA",
"postalcode":"10007",
"country":"USA",
"phone":"(206) 555-0103",
"mgrid":2
},
{
"empid":4,
"lastname":"Peled",
"firstname":"Yael",
"title":"Sales Representative",
"titleofcourtesy":"Mrs.",
"birthdate":"1957-09-19",
"hiredate":"2014-05-03",
"address":"5678 Old Redmond Rd.",
"city":"Redmond",
"region":"WA",
"postalcode":"10009",
"country":"USA",
"phone":"(206) 555-0104",
"mgrid":3
},
{
"empid":5,
"lastname":"Mortensen",
"firstname":"Sven",
"title":"Sales Manager",
"titleofcourtesy":"Mr.",
"birthdate":"1975-03-04",
"hiredate":"2014-10-17",
"address":"8901 Garrett Hill",
"city":"London",
"postalcode":"10004",
"country":"UK",
"phone":"(71) 234-5678",
"mgrid":2
},
{
"empid":6,
"lastname":"Suurs",
"firstname":"Paul",
"title":"Sales Representative",
"titleofcourtesy":"Mr.",
"birthdate":"1983-07-02",
"hiredate":"2014-10-17",
"address":"3456 Coventry House, Miner Rd.",
"city":"London",
"postalcode":"10005",
"country":"UK",
"phone":"(71) 345-6789",
"mgrid":5
},
{
"empid":7,
"lastname":"King",
"firstname":"Russell",
"title":"Sales Representative",
"titleofcourtesy":"Mr.",
"birthdate":"1980-05-29",
"hiredate":"2015-01-02",
"address":"6789 Edgeham Hollow, Winchester Way",
"city":"London",
"postalcode":"10002",
"country":"UK",
"phone":"(71) 123-4567",
"mgrid":5
},
{
"empid":8,
"lastname":"Cameron",
"firstname":"Maria",
"title":"Sales Representative",
"titleofcourtesy":"Ms.",
"birthdate":"1978-01-09",
"hiredate":"2015-03-05",
"address":"4567 - 11th Ave. N.E.",
"city":"Seattle",
"region":"WA",
"postalcode":"10006",
"country":"USA",
"phone":"(206) 555-0102",
"mgrid":3
},
{
"empid":9,
"lastname":"Doyle",
"firstname":"Patricia",
"title":"Sales Representative",
"titleofcourtesy":"Ms.",
"birthdate":"1986-01-27",
"hiredate":"2015-11-15",
"address":"1234 Houndstooth Rd.",
"city":"London",
"postalcode":"10008",
"country":"UK",
"phone":"(71) 456-7890",
"mgrid":5
}]';
SELECT * FROM OPENJSON (@json) 
WITH (
empid int '$.empid',
lastname varchar(100) '$.lastname',
firstname varchar(100) '$.firstname',
title varchar(100) '$.title',
titleofcourtesy varchar(100) '$.titleofcourtesy',
birthdate date '$.birthdate',
hiredate date '$.hiredate',
address varchar(300) '$.address',
city varchar(100) '$.city',
postalcode int '$.postalcode',
country char(2) '$.country',
phone varchar(20) '$.phone',
mgrid int '$.mgrid')
;

Рис. 7. Набор результатов из запроса HR.Employees

ISJSON

Функция ISJSON выполняет простой тест, чтобы убедиться, что текстовый документ представлен в допустимом формате JSON. В листинге 6 показаны два способа использования этой функции для проверки документа JSON. Сделав одно небольшое изменение в документе JSON, мы можем заставить SQL Server возвращать 0 (что означает: документ НЕ JSON) при выполнении этого запроса. Ради интереса я позволю вам разобраться с небольшим изменением, которое я внес в объект JSON (см. рис. 8a и 8b).

-- Listing 6 Using ISJSON
-- Basic Check for JSON Format
DECLARE @json NVARCHAR(4000) = N'
{
"empid":1,
"lastname":"Davis",
"firstname":"Sara",
"title":"CEO",
"titleofcourtesy":"Ms.",
"birthdate":"1968-12-08",
"hiredate":"2013-05-01",
"address":"7890 - 20th Ave. E., Apt. 2A",
"city":"Seattle",
"region":"WA",
"postalcode":"10003",
"country":"USA",
"phone":"(206) 555-0101"
}';
SELECT ISJSON (@json);

-- Check Using WITH Clause and CASE Expression
DECLARE @json NVARCHAR(4000) = N'
{
"empid":1,
"lastname":"Davis",
"firstname":"Sara",
"title":"CEO",
"titleofcourtesy":"Ms.",
"birthdate":"1968-12-08",
"hiredate":"2013-05-01",
"address":"7890 - 20th Ave. E., Apt. 2A",
"city":"Seattle",
"region":"WA",
"postalcode":"10003",
"country":"USA",
"phone":"(206) 555-0101"
}';
WITH JSONTEST as (SELECT ISJSON (@json) [IS JSON ?] )
SELECT 
CASE [IS JSON ?] 
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
END AS [IS JSON ?]
FROM JSONTEST;

Стоит отметить, что с помощью веб-сайтов, таких как https://jsonformatter.curiousconcept.com, вы можете быстро проверить текст JSON или отформатировать подготовленный текст как JSON.

JSON_* Функции

Чтобы продемонстрировать использование функций JSON_VALUE, JSON_QUERY и JSON_MODIFY, мы создаем таблицу со столбцом JSON, используя код из листинга 7. Обратите внимание, что тип рассматриваемого столбца — это обычный строковый тип данных NVACHAR(MAX). . SQL Server не имеет специального типа данных для данных JSON в реляционных таблицах.

-- Listing 7: Creating a Relational Table with JSON Data
USE [TSQLV4]
GO
/****** Object:  Table [HR].[Employees_JSON]    Script Date: 1/13/2020 10:03:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [HR].[Employees_JSON](
    [empid] [int] IDENTITY(1,1) NOT NULL,
    [lastname] [nvarchar](20) NOT NULL,
    [firstname] [nvarchar](10) NOT NULL,
    [title] [nvarchar](30) NOT NULL,
    [titleofcourtesy] [nvarchar](25) NOT NULL,
    [birthdate] [date] NOT NULL,
    [hiredate] [date] NOT NULL,
    [address] [nvarchar](60) NOT NULL,
    [city] [nvarchar](15) NOT NULL,
    [region] [nvarchar](15) NULL,
    [postalcode] [nvarchar](10) NULL,
    [country] [nvarchar](15) NOT NULL,
    [phone] [nvarchar](24) NOT NULL,
    [mgrid] [int] NULL,
    [jsondata] [nvarchar](max) NULL,
 CONSTRAINT [PK_Employees_JSON] PRIMARY KEY CLUSTERED 
(
    [empid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Insert one row in the HR.Employees_JSON Table
INSERT INTO [HR].[Employees_JSON](
lastname
, firstname
, title
, titleofcourtesy
, birthdate
, hiredate
, address
, city
, region
, postalcode
, country
, phone
, mgrid
, jsondata)
SELECT TOP 1 
lastname
, firstname
, title
, titleofcourtesy
, birthdate
, hiredate
, address
, city
, region
, postalcode
, country
, phone
, mgrid
,N'
{
"empid":1,
"lastname":"Davis",
"firstname":"Sara",
"title":"CEO",
"titleofcourtesy":"Ms.",
"birthdate":"1968-12-08",
"hiredate":"2013-05-01",
"address":"7890 - 20th Ave. E., Apt. 2A",
"city":"Seattle",
"region":"WA",
"postalcode":"10003",
"country":"USA",
"phone":"(206) 555-0101"
}'
FROM HR.Employees;

JSON_VALUE и JSON_QUERY кажутся похожими, но отличаются в том смысле, что JSON_VALUE извлекает скалярные значения из текста JSON, а JSON_QUERY извлекает объекты или массивы. Другими словами, вы, скорее всего, получите ошибку или NULL, если попытаетесь извлечь скалярное значение из текста JSON с помощью JSON_QUERY. JSON_MODIFY позволяет изменить определенное значение в тексте JSON, который хранится в столбце реляционной таблицы. В листинге 8 показаны простые примеры использования функций JSON_*. Попробовав это, вы заметите, что имя пути JSON чувствительно к регистру. Документация Microsoft показывает больше примеров использования этих функций.

-- Listing 8: JSON_* Samples 
-- Display A Single Columns Using JSON_VALUE
SELECT 
firstname
,lastname
,JSON_VALUE(jsondata,'$.title') AS Title
FROM HR.Employees_JSON
-- Display Two Columns Using JSON_VALUE
SELECT 
firstname
,lastname
,JSON_VALUE(jsondata,'$.title') AS Title,
JSON_VALUE(jsondata,'$.titleofcourtesy') AS TitleofCourtesy
FROM HR.Employees_JSON
-- Attempt QUerying a JSON Value Using JSON_QUERY (NULL Returned)
SELECT 
firstname
,lastname
,JSON_VALUE(jsondata,'$.title') AS Title,
JSON_QUERY(jsondata,'$.titleofcourtesy') AS TitleofCourtesy
FROM HR.Employees_JSON
-- Query a JSON Object Using JSON_QUERY
SELECT 
firstname
,lastname
,JSON_VALUE(jsondata,'$.title') AS Title,
JSON_QUERY(jsondata,'$') AS TitleofCourtesy
FROM HR.Employees_JSON
-- Attempt Querying a JSON Object Using JSON_VALUE (NULL Returned)
SELECT 
firstname
,lastname
,JSON_VALUE(jsondata,'$') AS Title
,JSON_QUERY(jsondata,'$') AS TitleofCourtesy
FROM HR.Employees_JSON;
-- Update a value in JSON text using JSON_MODIFY
DECLARE @jsondata varchar(max)
SELECT @jsondata= jsondata FROM HR.Employees_JSON;
SET @jsondata = JSON_MODIFY(@jsondata,'$.title','GCEO')
PRINT @jsondata
UPDATE HR.Employees_JSON 
SET jsondata=@jsondata;
SELECT 
firstname
,lastname
,JSON_VALUE(jsondata,'$.title') AS Title
FROM HR.Employees_JSON;

Вывод

SQL Server обеспечивает достаточную поддержку JSON, помогая преодолеть разрыв между миром SQL и миром без SQL. Функции, описанные в этой статье, максимально просты в освоении и реализации. В документации Microsoft есть больше примеров их использования, а также дополнительные функции. JSON и вообще No-SQL — это ценные знания, которые помогут в развитии карьеры современного администратора баз данных.

Ознакомьтесь с моим руководством Как работать с JSON в SQL Server для получения дополнительной информации.

Оригинальный источник:

Основы работы с JSON в SQL Server
В этом сообщении блога мы покажем вам, как использовать JSON в SQL Server.www.alibabacloud.com