SQL server: select all json array elements using JSON_QUERY

Question

I have a column myColumn in myTable table with this value:

"6285":[
         {
           "75963":{"lookupId":"54","value":"0","version":null},
           "75742":{"lookupId":"254","value":"991","version":null}
         }
       ]

I need to write select query using JSON_VALUE or JSON_QUERY functions (my sql server version does not support OPENJSON). The query should return this result:

"75963-0, 75742-991"

As you can see I need values of value parameter. Also note that I don't know what elements will an object inside 6285 array contain. I mean I wouldn't know in advance that there will be 2 elements (75963 and 75742) in it. There could be more or less elements and they could be different of course. However there will always be only one object in 6285 array.

What kind of select can I write to achieve it?

Answer 1

It's strange, I think that your version supports OPENJSON() and you may try to use the following statement. Note, that the JSON in the question is not valid, it should be inside {}.

Table:

CREATE TABLE Data (JsonColumn varchar(1000))
INSERT INTO Data (JsonColumn) 
VALUES ('{"6285":[{"75963":{"lookupId":"54","value":"0","version":null},"75742":{"lookupId":"254","value":"991","version":null}}]}')

Statement:

SELECT CONCAT(j2.[key], '-', JSON_VALUE(j2.[value], '$.value')) AS JsonValue
FROM Data d
CROSS APPLY OPENJSON(d.JsonColumn) j1
CROSS APPLY OPENJSON(j1.[value], '$[0]') j2

Result:

JsonValue
---------
75963-0
75742-991

If you need an aggregated result, you may use STRING_AGG():

SELECT STRING_AGG(CONCAT(j2.[key], '-', JSON_VALUE(j2.[value], '$.value')), ',') AS JsonValue
FROM Data d
CROSS APPLY OPENJSON(d.JsonColumn) j1
CROSS APPLY OPENJSON(j1.[value], '$[0]') j2

Result:

JsonValue
-----------------
75963-0,75742-991