Json conversion in SQL Server - multiple rows in to single json array

Question

Dataset :

create table grievances(grivanceid int ,grivancedesc varchar(10)) 
create table grievanceType(grivanceid int ,grivanceType varchar(10))  

insert into grievances values (1,'abc') 
insert into grievanceType values (1,'type1')
insert into grievanceType values (1,'type2')

Desired output:

{
    "grivanceid": 1,
    "grivancedesc": "abc",
    "grivanceType": [ "type1", "type2"]
}

My query : not fully achieved

select * 
from 
    (select 
         a.*, 
         stuff(list.grivanceType, 1, 1, '')  grivanceType 
     from 
         grievances a 
     cross apply 
         (select  
              ',' + grivanceType  
          from 
              grievanceType b  
          where  
              grivanceid = a.grivanceid  
          for xml path ('')
         ) list(grivanceType)) a 
for json path, without_array_wrapper

Does this answer your question? [SQL to JSON - array of objects to array of values in SQL 2016](https://stackoverflow.com/q/37708638/20299830) — Larnu, Apr 08 '21 at 17:04

John Cappelletti · Accepted Answer · 2021-04-08T17:51:10.780

1

It helps if you wrap your XML results in a JSON_Query()

Example

Select *
      ,grivanceType = JSON_QUERY('['+stuff((Select concat(',"',grivanceType,'"' )  
                                              From  grievanceType 
                                              Where grivanceid =A.grivanceid  
                                              For XML Path ('')),1,1,'')+']'
                              )
 From  grievances A
 for json path, without_array_wrapper

Returns

{
    "grivanceid": 1,
    "grivancedesc": "abc",
    "grivanceType": ["type1", "type2"]
}

edited Apr 08 '21 at 17:51

answered Apr 08 '21 at 17:37

John Cappelletti

71,300
6
42
62

@vignesh Never understood why the never added a simple array as a built-in function/feature. – John Cappelletti Apr 08 '21 at 17:54

Json conversion in SQL Server - multiple rows in to single json array

1 Answers1