CONCAT JSON array values in SQL-CodePudding

I'm trying to parse some JSON data in SQL. In the following JSON string I have an array called "val" which has multiple elements.


declare @JSONVariable nvarchar(max)
select @JSONVariable  = N'{"name":"North Carolina","region":"North East",
"category":[
    {"group_id":1AB,
        "groups":[{"val":[3242,4234],"make":{"type":"US","value":"1235"}}]},
    {"group_id":1AC,
        "groups":[{"val":[2354],"make":{"type":"US","value":"2342"}}]},
    {"group_id":1CS,
        "groups":[{"val":[242,3433,2424],"make":{"type":"US","value":"0656379"}}]},
    {"group_id":3AC,
        "groups":[{"val":[2463,4633],"make":{"type":"US","value":"3453"}}]}
    ]}'

I want to get all the elements in "Val" array as 1 comma separated string. Below is the query I wrote and I can only get the 1st element of the val array. How can I combine all the elements to a comma separated string?


SELECT a.*, aa.group_id, aa.val,aa.make_type,aa.make_value
FROM OPENJSON(@JSONVariable, '$.category') a
CROSS APPLY OPENJSON(aa.value)
WITH (    
        group_id nvarchar(100) '$.group_id',
        val nvarchar(max)  '$.groups[0].val[0]', -- need all the values in val array comma separated
        make_type nvarchar(10) '$.groups[0].make.type',
        make_value nvarchar(100) '$.groups[0].make.value'
    ) aa

CodePudding user response：

If we can assume you have valid JSON, then you simply need to remove the positional operator from val:


Declare @JSONVariable nvarchar(max)
select @JSONVariable  = N'{"name":"North Carolina","region":"North East",
"category":[
    {"group_id":"1AB",
        "groups":[{"val":[3242,4234],"make":{"type":"US","value":"1235"}}]},
    {"group_id":"1AC",
        "groups":[{"val":[2354],"make":{"type":"US","value":"2342"}}]},
    {"group_id":"1CS",
        "groups":[{"val":[242,3433,2424],"make":{"type":"US","value":"0656379"}}]},
    {"group_id":"3AC",
        "groups":[{"val":[2463,4633],"make":{"type":"US","value":"3453"}}]}
    ]}';



SELECT c.group_id,
       c.val,
       c.make_type,
       c.make_value
FROM OPENJSON(@JSONVariable, '$.category') 
WITH (group_id nvarchar(100) '$.group_id',
      val nvarchar(max)  '$.groups[0].val' AS JSON, --Should these groups also only be position 0?
      make_type nvarchar(10) '$.groups[0].make.type',
      make_value nvarchar(100) '$.groups[0].make.value') c --aa for category doesn't make much sense. We'll go with c for category

This brings the value back as a JSON array, rather than as a comma separated value. If you need to have it as a just CSV, you simply need to replace the brakets; which can easily be done with a couple of nested REPLACEs.