1

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
vignesh
  • 1,414
  • 5
  • 19
  • 38
  • 1
    Does this answer your question? [SQL to JSON - array of objects to array of values in SQL 2016](https://stackoverflow.com/q/37708638/20299830) – Thom A Apr 08 '21 at 17:04

1 Answers1

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"]
}
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66