Расщепление строк с помощью OPENJSON

Пересказ статьи Dave Mason. Splitting Strings With OPENJSON

Начиная с SQL Server 2016, Майкрософт предоставляет функцию STRING_SPLIT. Это табличнозначная функция, которая разбивает символьную строку на ряды подстрок на основе заданного символа-разделителя. Это было приятное добавление, которого долго ждали. Но имеется один недостаток: порядок выходных строк не гарантирует совпадение с порядком подстрок во входной строке.

Майкрософт также обеспечивает поддержку парсинга данных 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.

Добавить комментарий