Due to the tag [tsql]
and the usage of OPENJSON
I assume this is SQL-Server. But might be wrong... Please always specify your RDBMS (with version).
Your JSON is rather weird... I think you've overdone it while trying to simplify this for brevity...
Try this:
DECLARE @tbl TABLE(ID INT IDENTITY,YourJSON NVARCHAR(MAX));
INSERT INTO @tbl VALUES(N'{ "arr": ["str1 - str2"] }') --weird example...
,(N'{ "arr": ["a","b","c"] }'); --array with three elements
SELECT t.ID
,B.[value] AS arr
FROM @tbl t
CROSS APPLY OPENJSON(YourJSON)
WITH(arr NVARCHAR(MAX) AS JSON) A
CROSS APPLY OPENJSON(A.arr) B;
A rather short approach (but fitting to this simple example only) was this:
SELECT t.ID
,A.*
FROM @tbl t
OUTER APPLY OPENJSON(JSON_QUERY(YourJSON,'$.arr')) A
Hint
JSON support was introduced with SQL-Server 2016
UPDATE: If the JSON's content is a weird CSV-string...
There's a trick to transform a CSV into a JSON-array. Try this
DECLARE @tbl TABLE(ID INT IDENTITY,YourJSON NVARCHAR(MAX));
INSERT INTO @tbl VALUES(N'{ "arr": ["str1 - str2"] }') --weird example...
,(N'{ "arr": ["a","b","c"] }') --array with three elements
,(N'{ "arr": ["x-y-z"] }'); --array with three elements in a weird CSV format
SELECT t.ID
,B.[value] AS arr
,C.[value]
FROM @tbl t
CROSS APPLY OPENJSON(YourJSON)
WITH(arr NVARCHAR(MAX) AS JSON) A
CROSS APPLY OPENJSON(A.arr) B
CROSS APPLY OPENJSON('["' + REPLACE(B.[value],'-','","') + '"]') C;
Some simple replacements in OPENJSON('["' + REPLACE(B.[value],'-','","') + '"]')
will create a JSON array out of your CSV-string, which can be opened in OPENJSON.
{ "arr": ["str1 - str2"] }
is an array holding a single string.{ "arr": ["str1","str2"] }
is an array with 2 strings. Did you mean the latter, or is the former correct?SPLIT
in SQL Server. There's a STRING_SPLIT in SQL Server 2016+. You should probably fix the JSON contents though. If you want an array of strings, why use["str1 - str2"]
instead of["str1","str2"]
?