SQL Server 2014 Read json values within an array held in NVARCHAR column

Question

I have a table with 3 columns:

id INT, name NVARCHAR(50), myData NVARCHAR(MAX)

myData just holds a json string array something like

["Fingers"]
["Fingers","Right-"]
["Arm","Fingers","Left-"]

I want to select all the values in 1 column, such as

Fingers
Fingers
Right-
Arm
Fingers
Left-

How can I go about this? I do not believe SQL Server 2014 can read JSON data.

Once this is done, I can select each unique value by doing a SELECT DISTINCT statement.

I need to be able to do this with T-SQL, and cannot create any functions to cope with this, must be T-SQL only.

Any help much appreciated.

Answer 1

Please use this, where [#json] is your original table:

;with [data] as
(
    select [mydata] = replace(replace(replace([mydata], '[', SPACE(0)), ']', space(0)), '"', space(0)) from [#json]
)
,[split] as
(
    select
        [mydata] = [s].[str]
    from
        [data] as [d]
    cross apply
        (       
            select 
                [str] = [x].[c].[value]('(./text())[1]', 'nvarchar(4000)')
            from 
                ( 
                    select [x] = convert(xml, '<i>' + replace([d].[mydata], ',', '</i><i>') + '</i>').[query]('.')
                ) as [a] 
            cross apply 
                [x].[nodes]('i') as [x]([c])
        ) as [s]
)
select 
    [mydata] 
from 
    [split];

Full testing query: https://pastebin.com/r4AwxPYS