0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gillardo
  • 9,518
  • 18
  • 73
  • 141
  • I don't think that's a valid json format? Doesn't JSON require key:value pairs? – Mason Stedman Apr 28 '18 at 09:32
  • i think your JSON look like `[ { "Array": [ {"value":"Fingers"}, {"value":[{"value":"Fingers"},{"value":"Right-"}]}, {"value":[{"value":"Arm"},{"value":"Fingers"},{"value":"Left-"}]} ] } ]` . OPENJSON perfect for your solution for that refer : https://learn.microsoft.com/en-us/sql/relational-databases/json/solve-common-issues-with-json-in-sql-server?view=sql-server-2017#Anchor_6 please look at https://stackoverflow.com/a/37218450/6923146 – Hardik Masalawala Apr 28 '18 at 10:03
  • @HardikMasalawala json supported from SqlServer 2016. Problem is, that we are on 2014 :) – Juozas Apr 28 '18 at 10:13

1 Answers1

2

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

Juozas
  • 916
  • 10
  • 17