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.

I don't think that's a valid json format? Doesn't JSON require key:value pairs? — Mason Stedman
– Mason Stedman, Commented Apr 28, 2018 at 9: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 : learn.microsoft.com/en-us/sql/relational-databases/json/… please look at stackoverflow.com/a/37218450/6923146 — Hardik Masalawala
– Hardik Masalawala, Commented Apr 28, 2018 at 10:03
@HardikMasalawala json supported from SqlServer 2016. Problem is, that we are on 2014 :) — Juozas
– Juozas, Commented Apr 28, 2018 at 10:13

Juozas · Accepted Answer · 2018-04-28 10:12:33Z

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

answered Apr 28, 2018 at 10:12

Juozas

94310 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gillardo Over a year ago

This is great.... Just asking.. is it possible without the WITH? Just use a SELECT statement?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related