Splitting Strings With OPENJSON

Starting with SQL Server 2016, Microsoft provided a STRING_SPLIT function. It is a table-valued function that splits a string into rows of substrings, based on a specified separator character. It's been a welcome addition that we waited a long time for. It has one shortcoming, though: the order of the output rows is not guaranteed to match the order of the substrings in the input string.

Microsoft also provided support for parsing JSON data starting with SQL Server 2016. I discovered the OPENJSON function can be used to split strings, and it can also return the ordinal position of each substring from the original input string. Here is a simple example of the OPENJSON function:

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

The "Players" and "Jersey Numbers" keys both have arrays for values. The arrays themselves are JSON data and can be passed directly to the OPENJSON function. Let's start with the "Jersey Numbers" array:

--Split integer string.
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

The query returns the key and value columns of the OPENJSON function (aliased as ArrayIndex and ElementValue respectively). Each integer is returned as a separate row, along with the zero-based ordinal position of the integer from the original string. The integer element values can be ordered, keeping the proper array index key:

--Split integer string, order by integer value.
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)

If we wanted to select a specific element from the array, we can use a WHERE clause:

--Split integer string, select only the 4th element.
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

Perhaps even better, we can use the JSON_VALUE function to select a specific element from an array, and without splitting the string:

--Select the 4th element directly via JSON_VALUE
DECLARE @JsonArray NVARCHAR(MAX) = '[42, 0, 7, 11, 36, 20, 13, 12]';
SELECT JSON_VALUE(@JsonArray, '$[3]') AS ElementValue;

Strings

All of the examples above work with string arrays. Here is an example with player names:

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

The strings must be enclosed in double quotes to be properly formatted JSON data, however. If they are omitted, an error occurs:

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.

Using the REPLACE function to fix up TSQL string data is an option that comes to mind. Admittedly, additional lines of code (and possibly precious CPU cycles) are necessary. You'll have to decide if this is worth the extra effort.

Splitting strings with JSON is an interesting option for a problem that has mostly been solved with STRING_SPLIT. I might use this option, but only if I need to guarantee ordinal position of substring elements. Otherwise, I'll stick with STRING_SPLIT. Happy coding!