1

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?

Mukhamedali Zhadigerov
  • 811
  • 1
  • 15
  • 32
  • What is your SQL Server version? If you can use `JSON_QUERY()`, you should be able to use `OPENJSON()`. – Zhorov Jul 07 '20 at 12:57
  • 1
    @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 (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 Jul 07 '20 at 13:01

1 Answers1

2

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
Zhorov
  • 28,486
  • 6
  • 27
  • 52