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?

What is your SQL Server version? If you can use JSON_QUERY(), you should be able to use OPENJSON(). — Zhorov
– Zhorov, Commented Jul 7, 2020 at 12:57
@Zhorov Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor). Maybe it is possible to use OPENJSON in sql editor, but I am querying using Hibernate (Java framework) and it does not support OPENJSON I think. But If you could write a query using OPENJSON it would also help. — Mukhamedali Zhadigerov
– Mukhamedali Zhadigerov, Commented Jul 7, 2020 at 13:01

Zhorov · Accepted Answer · 2020-07-07 13:24:26Z

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

Collectives™ on Stack Overflow

SQL server: select all json array elements using JSON_QUERY

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related