SQL-Ex blog

Майкрософт также обеспечивает поддержку парсинга данных JSON, начиная с SQL Server 2016. Я обнаружил, что функция OPENJSON может быть использована для расщепления строк, и она может также возвращать номер каждой подстроки из исходной входной строки. Вот простой пример функции OPENJSON:

DECLARE @JsonData NVARCHAR(MAX) = '{  

   "Team":"Boston Celtics",  

   "Season":"2018-2019",   

   "Players":["Al Horford","Jayson Tatum","Jaylen Brown","Kyrie Irving","Marcus Smart", "Gordon Hayward", "Marcus Morris","Terry Rozier"],  

   "Jersey Numbers":[42, 0, 7, 11, 36, 20, 13, 12]

}';

SELECT j.[key], j.value

FROM OPENJSON(@JsonData) AS j

Оба ключа - "Players" и "Jersey Numbers" - имеют массив значений. Сами массивы являются данными JSON и могут быть переданы непосредственно в функцию OPENJSON. Давайте начнем с массива "Jersey Numbers":

--Расщепление строки целых чисел.

DECLARE @JsonArray NVARCHAR(MAX) = '[42, 0, 7, 11, 36, 20, 13, 12]';

SELECT j.[key] AS ArrayIndex, j.value AS ElementValue

FROM OPENJSON(@JsonArray) AS j

Запрос возвращает столбцы ключа и значения OPENJSON (с алиасами ArrayIndex и ElementValue соответственно). Каждое целое число возвращается в отдельной строке, наряду с номером числа в исходной строке (нумерация от 0). Элементы целочисленных значений могут быть упорядочены с сохранением надлежащего ключа индекса массива:

--Расщепление строки целых чисел, упорядоченных по значениям.

DECLARE @JsonArray NVARCHAR(MAX) = '[42, 0, 7, 11, 36, 20, 13, 12]';

SELECT j.[key] AS ArrayIndex, j.value AS ElementValue

FROM OPENJSON(@JsonArray) AS j

ORDER BY CAST(j.value AS INT)

Если мы хотим выбрать конкретный элемент массива, то можем использовать предложение WHERE:

--Расщепление строки целых чисел, выбор одного 4-го элемента.

DECLARE @JsonArray NVARCHAR(MAX) = '[42, 0, 7, 11, 36, 20, 13, 12]';

SELECT j.[key] AS ArrayIndex, j.value AS ElementValue

FROM OPENJSON(@JsonArray) AS j

WHERE j.[key] = 3

Будет даже лучше, если мы используем функцию JSON_VALUE для выбора конкретного элемента массива, и без расщепления строки:

--Выбор 4-го элемента непосредственно с помощью JSON_VALUE

DECLARE @JsonArray NVARCHAR(MAX) = '[42, 0, 7, 11, 36, 20, 13, 12]';

SELECT JSON_VALUE(@JsonArray, '$[3]') AS ElementValue;

Все рассмотренные выше примеры работают с массивами строк. Вот пример с именами игроков:

DECLARE @JsonArray NVARCHAR(MAX) = '["Al Horford","Jayson Tatum","Jaylen Brown","Kyrie Irving","Marcus Smart", "Gordon Hayward", "Marcus Morris","Terry Rozier"]';

SELECT j.[key] AS ArrayIndex, j.value AS ElementValue

FROM OPENJSON(@JsonArray) AS j

Строки должны быть взяты в двойные кавычки в соответствии с правильным форматированием данных JSON. Если они опущены, возникает ошибка:

DECLARE @JsonArray NVARCHAR(MAX) = '[Al Horford,Jayson Tatum,Jaylen Brown,Kyrie Irving,Marcus Smart, Gordon Hayward, Marcus Morris,Terry Rozier]';

SELECT j.[key] AS ArrayIndex, j.value AS ElementValue

FROM OPENJSON(@JsonArray) AS j



Msg 13609, Level 16, State 4, Line 39

JSON text is not properly formatted. Unexpected character 'A' is found at position 1.

На ум приходит использование функции REPLACE для адаптации строк T-SQL, если это подходящий вариант для вас.

Расщепление строк с помощью JSON представляется интересным вариантом для задач, которые обычно решались с помощью функции STRING_SPLIT. Я могу использовать этот вариант, но только если мне требуется гарантировать исходный порядок подстрок. В противном случае я буду бить функцией STRING_SPLIT.