SQL-Ex blog

Это поверхностный взгляд на возможности языка, поскольку я все еще экспериментирую и изучаю их. Я оцениваю SQL Server 2022 с точки зрения тех, кто стоит перед решением, стоит ли выполнять апгрейд системы. Поэтому я исследую вопрос, насколько могут облегчить написание кода эти изменения языка. Я провожу эксперименты для этой статьи на SQL Server 2022 RTM.

Возможности JSON

SQL Server привнес многое в T-SQL для работы с JSON в версии 2022. Вот некоторые новые функции:

• JSON_PATH_EXISTS()


• JSON_OBJECT()


• JSON_ARRAY()

Начиная с версии 2016, в SQL Server имелись и другие функции - JSON_VALUE(), JSON_QUERY(), JSON_MODIFY(), но они не изменились. Только одна из ранее существующих функций, ISJSON(), была улучшена. Здесь мы рассмотрим три упомянутых новых функций и ISJSON().

JSON_OBJECT()

Функция JSON_OBJECT() является новой для SQL Server 2022 и предназначена для конструирования текста объекта JSON из нуля или большего числа выражений. Существенно то, что эта функция построит валидный документ JSON с внешними фигурными скобками и различными парами ключ-значение внутри. Вместо использования конкатенации строк для сборки документа JSON, вы можете с помощью этой функции собрать вместе предоставленный список пар ключ-значение.

Эта функция принимает произвольное число аргументов. Каждый из этих аргументов представлен как пара с выражениями ключа в форме «строка1 : строка2». Каждые две строки или переменных разделяются двоеточием. Затем каждая пара аргументов разделяется запятой. Это подразумевает, что значение единственного ключа должно передаваться таким образом:

select JSON_OBJECT(@s1 : @s2)

Если требуется передать две пары вместе, то используется такая конструкция:

select JSON_OBJECT(@s1 : @s2, @s3 : @s4)

Последующие пары должны следовать тому же формату, используя запятую и две строки, разделенные двоеточием. Если передать только один аргумент, возвращается ошибка. Я не вижу ограничения на число параметров. Я успешно тестировал до 5000 пар ключ-значение.

Кроме того, дополнительно может быть включено предложение JSON NULL. Оно определяет то, как обрабатывать NULL-значения. Имеется два варианта этого предложения:

• NULL ON NULL


• ABSENT ON NULL

Если используется первый параметр, то SQL NULL конвертируется в JSON NULL. Если используется второй вариант, то это значение опускается.

Эта функция возвращает строку JSON, которая имеет тип NVARCHAR(MAX).

Давайте посмотрим, как можно использовать эту функцию. Если вызвать эту функцию без аргументов, она работает, но я не уверен, что это может оказаться полезным.



Давайте вызовем со скалярными значениями. Я передам две строк, и посмотрим, что получится. Я буду использовать свое имя в качестве значения с ключом "name". Вот код вызова:

select json_object( 'fname' : 'Frank')

Вот результат:

{"fname":"Frank"}

Если не заключить в кавычки ключ (или значение), возникнет ошибка, например (fname без кавычек):

Msg 207, Level 16, State 1, Line 1
Invalid column name 'fname'.
(Недопустимое имя столбца)

Можно использовать также переменные, например:

declare @s1 varchar(100),

        @s2 varchar(100);

select @s1 = 'fname'

select @s2 = 'Frank'

select JSON_OBJECT(@s1:@s2)

Будет получен тот же результат, что и в примере со скалярами выше. Для двух пар ключ-значение можно использовать такой код:

declare @s1 varchar(100),

        @s2 varchar(100);

select @s1 = 'name'

select @s2 = 'Frank'

selectJSON_OBJECT(@s1:@s2)

declare @s3 varchar(100),

        @s4 varchar(100);

select @s3 = 'language'

select @s4 = 'PowerShell'

select JSON_OBJECT(@s1 : @s2, @s3 : @s4)

Результат будет таким:

{"name":"Frank","language":"PowerShell"}

Насколько это полезно? И полезно ли вообще?

Ну, сообщение об ошибке выше дает ключ к тому, как можно это использовать. Функция ожидала столбец, поэтому попробуем это. У меня есть таблица SalesOrders (из предыдущей статьи), которая выглядит так:



Давайте попробуем использовать столбец в этой функции. Я буду использовать сначала SaleDate, так:

select JSON_OBJECT('SaleDate':SaleDate) 

from SalesOrder

go

Это даст мне следующие значения:

{"SaleDate":"2020-06-01T00:00:00"}
{"SaleDate":"2020-06-01T00:00:00"}
{"SaleDate":"2020-06-05T00:00:00"}
{"SaleDate":"2020-06-05T00:00:00"}
{"SaleDate":"2020-06-05T00:00:00"}
{"SaleDate":"2020-06-05T00:00:00"}
{"SaleDate":"2020-06-09T00:00:00"}
{"SaleDate":"2020-06-15T00:00:00"}
{"SaleDate":"2020-06-15T00:00:00"}
{"SaleDate":"2020-06-15T00:00:00"}

Уже интересно. Я также могу использовать несколько столбцов. Допустим мне нужны SaleID, SaleDate и CustomerID. Можно использовать такой код:

select JSON_OBJECT('SaleID' : SaleID ,'SaleDate':SaleDate, 'CustomerID': CustomerID) 

from SalesOrder

go

Это дает мне набор документов:

{"SaleID":1,"SaleDate":"2020-06-01T00:00:00","CustomerID":1}
{"SaleID":1,"SaleDate":"2020-06-01T00:00:00","CustomerID":1}
{"SaleID":2,"SaleDate":"2020-06-05T00:00:00","CustomerID":4}
{"SaleID":3,"SaleDate":"2020-06-05T00:00:00","CustomerID":6}
{"SaleID":3,"SaleDate":"2020-06-05T00:00:00","CustomerID":6}
{"SaleID":3,"SaleDate":"2020-06-05T00:00:00","CustomerID":6}
{"SaleID":4,"SaleDate":"2020-06-09T00:00:00","CustomerID":7}
{"SaleID":5,"SaleDate":"2020-06-15T00:00:00","CustomerID":1}
{"SaleID":5,"SaleDate":"2020-06-15T00:00:00","CustomerID":1}
{"SaleID":5,"SaleDate":"2020-06-15T00:00:00","CustomerID":1}

Интересно то, что я могу использовать также вложенную функцию. Это даст мне более сложный документ. Предположим мне нужен документ с тремя парами ключ-значение: SaleID, CustomerID и OrderDetails. OrderDetails содержит свой собственный документ, который имеет дату, количество и цену. Я могу выполнить такой код:

select JSON_OBJECT('SaleID' : SaleID ,'CustomerID': CustomerID, 'OrderDetails': JSON_OBJECT('SaleDate':SaleDate,'Qty': Qty, 'Price':Price)) 

from SalesOrder

go

А вот результаты:



Еще интересней. Конечно, я захотел бы получить все это в одном документе. Для этого потребовалась бы некоторая обработка результирующего набора или конкатенация многих строк. Однако это лучше делать на клиенте. Я могу также использовать здесь функцию JSON_ARRAY(), которую буду обсуждать следом.

Но прежде, чем я сделаю это, как насчет предложения NULL? Давайте его протестируем. Я создам новую таблицу с тремя строками, которая выглядит так:

appttime title
---------- ----------
Mon 10am Standup
Tue 10am Standup
Fri 4pm NULL

Если мне нужны эти документы, я могу использовать следующий код с предложением NULL ON NULL:

select json_object( 'Appt' : appttime, 'title':title NULL ON NULL)

from schedule

Вот результат:

{"Appt":"Mon 10am","title":"Standup"}
{"Appt":"Tue 10am","title":"Standup"}
{"Appt":"Fri 4pm","title":null}

Если я выберу другой вариант, то увижу следующее (результаты после пунктирной линии):

select json_object( 'Appt' : appttime, 'title':title ABSENT ON NULL)

from schedule

-----

{"Appt":"Mon 10am","title":"Standup"}

{"Appt":"Tue 10am","title":"Standup"}

{"Appt":"Fri 4pm"}

Как видно, я могу либо включить JSON NULL, либо просто отбросить пару ключ-значение.

JSON_ARRAY()

Это еще одна новая функция. JSON_ARRAY() предназначена для конструирования валидного массива JSON из входных параметров. Как и предыдущая функция, она может использовать переменное число аргументов для построения массива. Массив JSON представляет собой набор значений в квадратных скобках, разделенных запятыми. Также имеется необязательное предложение JSON NULL.

Как и ранее, если отсутствуют аргументы, возвращается пустой результат. В этом случае - только две квадратных скобки, которые представляют собой пустой массив. Вот код и результат (после пунктирной линии).

select JSON_ARRAY()

---------

[]

Если использовать набор значений, они образуют массив:

select json_array(1,2,3,4)

Вот результат:



А если использовать столбец с числами. Я выберу его из таблицы dbo.SalesOrder:

select top 5 json_array(CustomerID)

 from SalesOrder

 GO

Результаты не те, что я ожидал:



Это не кажется очень полезным. А если я передам несколько столбцов из таблицы? Я могу сделать так, чтобы возвращалось несколько значений.

select top 5 json_array(productid, qty,price)

 from SalesOrder

 GO

Результаты более интересны.

[50,1,100.0000]
[51,10,50.0000]
[52,5,10.0000]
[53,10,6.0000]
[54,6,6.0000]

А полезны? Я не уверен. Как узнать, что эти значения в массиве представляют собой? Я подозреваю, что большинство JSON не так чтобы документированы, но это кажется еще хуже, если у нас не содержатся одни и те же по смыслу данные в нескольких столбцах, что было бы нарушением требований нормализации при проектировании. Опять таки, множество баз данных плохо нормализованы. Как разработчику, мне может понравится работать с подобной структурой.

Последний тест. Я буду конкатенировать строки таблицы в символьную строку, а затем передавать значения в функцию JSON_ARRAY().

with cteString (Customers)

as

(SELECT DISTINCT

   STUFF((SELECT

              ',' + cast( c2.CustomerID  as varchar(10)) as Customers

          FROM

              SalesOrder c2

          FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')

FROM SalesOrder c1

)

select json_array(Customers)

from cteString

- - - - - 

["1,1,4,6,6,6,7,1,1,1"]

Это неважно работает. Не уверен, что я буду использовать эту функцию, но она может понадобиться вам.

JSON_PATH_EXISTS()

JSON_PATH_EXISTS() - это новая функция в SQL Server 2022. Эта функция предназначена для тестирования пути в строке JSON и сообщает, если он существует. Возможными результатами являются:

• NULL, если передается NULL


• 1 - путь существует


• 0 - в противном случае

Как бы хорошо, что эта функция не возвращает ошибки, т.к. не требуется обработка ошибок, но вам потребуется обработка результата для этих трех случаев. Я бы предпочел, чтобы она возвращала только 0 или 1. В конце концов, если я передам строку NULL JSON, пути не будет.

Давайте испытаем ее на нескольких примерах. Я буду использовать этот документ в моих экспериментах. Я буду выполнять это с другим кодом, но не хочу повторять его в каждом примере.

declare @d varchar(max) = '{

    "book":{

            "isbn":"9781593279509",

            "title":"Eloquent JavaScript, Third Edition",

            "author":"Marijn Haverbeke",

            "website":"http://eloquentjavascript.net/"

        },

      "magazines": [ 

                {

                     "title":"SQL Server Pro",

                     "publisher":"Zinio"

},

                {

                     "title":"SQL Server Geeks",

                     "publisher":"DPS"

}

   ],

"websites": [ {

              "title":"SQL Server Central",

  "url":"https://www.sqlservercentral.com"

  },

  {

              "title":"SQL Blog",

  "URL":"https://sqlblog.org/"

  }

]

}'

Путь JSON имеет конкретный формат. Он начинается с $ в качестве корня, а затем точка (период) разделяет имя каждого ключа. Посмотрите примеры:

select JSON_PATH_EXISTS(@d, '$.book')

-- возвращает 1

Путь - от корня к book, который является первым ключом. Если мне требуется websites, я бы использовал:

select JSON_PATH_EXISTS(@d, '$.websites')

-- возвращает 1

Если заглянуть под books, то можно увидеть внутри этого ключа другие ключи. Например, isbn является ключом ниже ключа book. Его путем является:

select JSON_PATH_EXISTS(@d, '$.book.isbn')

-- возвращает 1

Описки имеют значение, включая регистр. Примеры - ниже:

-- оба возвращают 0

select JSON_PATH_EXISTS(@d, '$.book.isnb')

select JSON_PATH_EXISTS(@d, '$.book.ISBN')

Как насчет URL веб-сайтов? Я не могу выполнить это.

select JSON_PATH_EXISTS(@d, '$.websites.title')

-- возвращает 0

На самом деле я могу это сделать. Не возвращает ошибок, помните? Этот запрос возвращает 0, поскольку нет пути от ключа websites к ключу title. Вместо этого мне нужен индекс массива. Индексация в JSON начнается с нуля, поэтому фактическим путем является websites[0].title. Этот код возвращает 1.

select JSON_PATH_EXISTS(@d, '$.websites[0].title')

-- возвращает 1

Однако и здесь регистр имеет значение. Взгляните на документ. Два адреса в websites имеют различный регистр. Это означает, что следующий код вернет 1 и 0.

select JSON_PATH_EXISTS(@d, '$.websites[0].url'),

       JSON_PATH_EXISTS(@d, '$.websites[1].url')

-- возвращает 1 и 0

Если я в обоих запросах использую верхний регистр, то получу 0 и 1.

select JSON_PATH_EXISTS(@d, '$.websites[0].URL'),

       JSON_PATH_EXISTS(@d, '$.websites[1].URL')

-- возвращает 0 и 1

Я проверил проблему с ошибкой, и это неправильно. Следующее вызывает ошибку:

select JSON_PATH_EXISTS(@d, 'dfgdfgfdgd')

В SSMS выводится:

Unexpected character 'd' is found at position 0.
(Неожиданный символ 'd' обнаружен в позиции 0.)

Ладно.

Если я передам документ NULL, то получу NULL.

declare @d varchar(max)

select JSON_PATH_EXISTS(@d, '$.book')

go

-- возвращает NULL

Думаю, что это полезно, когда вы хотите проверить наличие ключа. Поскольку документы JSON формируются приложениями и зачастую без принудительной схемы, это означает, что вы должны проверить ключи, прежде чем обрабатывать их. Для меня это один из недостатков NoSQL, схемы чтения элементов. Требуется множество проверок при чтении, которые могут сделать более сложной обработку и создать технические проблемы в коде.

ISJSON()

Функция ISJSON() существовала и до SQL Server 2022, но она была улучшена в этой версии. Теперь мы можем указать тип ожидаемого выражения JSON и проверить, что он имеется. До SQL Server 2022 имелся только один параметр, которым было строковое выражение. Мы имели это в SQL Server 2017 (который часто используется):

DECLARE @d VARCHAR(1000) = '

{ "book":1

}'

SELECT ISJSON(@d)

-- возвращает 1

SELECT @d = '"book":1'

SELECT ISJSON(@d)

-- возвращает 0

В SQL Server 2022 мы получили необязательный второй параметр, который может принимать одно из четырех значений: VALUE, SCALAR, OBJECT, ARRAY. Каждое из этих значений соответствует типу структуры JSON. Вот их определения (из документации):



Давайте проверим это с помощью кода, приведенного выше. Первый документ должен быть валидным объектом JSON и значением. Это не скаляр и не массив.

DECLARE @JSON VARCHAR(1000) = '

{ "book":1

}'

select isjson(@JSON, VALUE) as JSONValue,

       isjson(@JSON, SCALAR) as JSONSCALAR,

       isjson(@JSON, ARRAY) as JSONARRAY,

       isjson(@JSON, OBJECT) as JSONOBJECT

-- возвращает

-- JSONValue JSONSCALAR JSONARRAY JSONOBJECT

-- 1          0          0         0

А что со вторым кодом? Это только ключ и значение. Это не должно быть чем-то. Это невалидный JSON, и не значение или скаляр. Это не массив и не объект, т.к. не имеет скобок.

SELECT @JSON = '"book:1"'

select isjson(@JSON, VALUE) as JSONValue,

       isjson(@JSON, SCALAR) as JSONSCALAR,

       isjson(@JSON, ARRAY) as JSONARRAY,

       isjson(@JSON, OBJECT) as JSONOBJECT

-- возвращает

-- JSONValue JSONSCALAR JSONARRAY JSONOBJECT

-- 0          0          0         0

Давайте проверим кое-что другое. Я включу значение JSON в качестве первого результата, а затем эти четыре результата. Считайте, что этот код был передан в функцию ISJSON().



Интересный тест, и он сочетает возможность тестирования документа JSON с его фрагментом, например, массивом. Я не считаю эти дополнительные элементы очень ценными, но полагаю полезной возможностью проверить, не является ли нечто объектом JSON или часть строки - массивом. Вам потребовалась бы функция SUBSTRING() для вырезания части массива, но это не так трудно. Утомительно, но не трудно.

Заключение

В этой статье рассматриваются улучшения обработки JSON в T-SQL. Мы обсудили функции JSON_OBJECT() и JSON_ARRAY(), которые возвращают документы JSON на основе входных параметров, функции JSON_PATH_EXISTS() и ISJSON() для тестирования документов JSON или их частей.

Не важно, нравится вам JSON или нет, это то, что нравится разработчикам. Это значит, что он становится реальностью в базах данных любых типов. Полезно изучить эти функции, чтобы лучше понимать, как вы можете обрабатывать документы JSON при необходимости.

Ссылки по теме

1. OPENJSON и CROSS APPLY


2. JSON_PATH_EXISTS дает вам больше власти над документами JSON в SQL Server


3. Парсинг данных типа JSON в SQL Server


4. JSON для SQL Server. Часть 1