Sql Query to get an array having no column name in json result

Question

I have a table with following fields

 Id     RequestId     CategoryId
 1      112           1
 2      123           1
 3      123           2

SELECT      R.RequestId,
            (SELECT RC.CategoryId FROM Request RC WHERE RC.Id = R.Id FOR JSON AUTO) AS Categories
FROM        Request R

Above query returns the data as mentioned below

 RequestId     Categories
 112           [{"CategoryId":"1"}]
 123           [{"CategoryId":"1"},{"CategoryId":"2"}]

But, I want that column name CategoryId should not be repeated for every item in json array. Thus, my expected result is:

 RequestId     Categories
 112           ["1"]
 123           ["1","2"]

Hi, what was the best solution for this? – Soerman Oct 25 '22 at 08:22 — Soerman, Oct 25 '22 at 08:22

score 3 · Answer 1 · answered Jul 24 '18 at 08:08

Was used: SQL to JSON - array of objects to array of values in SQL 2016

create table Request (
  Id int,
  RequestId int,
  CategoryId int
)
GO

insert into Request (Id,RequestId,CategoryId) values
( 1,      112,           1),
( 2,      123,           1),
( 3,      123,           2);
GO

SELECT distinct R.RequestId,
            (
SELECT  
  JSON_QUERY('[' + STUFF(( SELECT ',' + '"' + convert(varchar(10), RC.CategoryId) + '"' 
FROM Request RC
WHERE RC.RequestId = R.RequestId
FOR XML PATH('')),1,1,'') + ']' ) Categories  
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER 
            ) AS Categories
FROM Request R
GO

RequestId | Categories              
--------: | :-----------------------
      112 | {"Categories":["1"]}    
      123 | {"Categories":["1","2"]}

SELECT  distinct R.RequestId,

JSON_QUERY(
            (
SELECT  
  JSON_QUERY('[' + STUFF(( SELECT ',' + '"' + convert(varchar(10), RC.CategoryId) + '"' 
FROM Request RC
WHERE RC.RequestId = R.RequestId
FOR XML PATH('')),1,1,'') + ']' ) Categories  
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER 
            )
, '$.Categories' )
FROM Request R
GO

RequestId | (No column name)
--------: | :---------------
      112 | ["1"]           
      123 | ["1","2"]

db<>fiddle here

Thanks for your answer. But, STUFF makes query execution really very slow when data is huge. Any thoughts about it? — M.S., Jul 24 '18 at 11:24

Roman Pekar · Answer 2 · 2019-04-05T20:41:40.123

As far as I know, you need to aggregate string yourself and then convert it to json. For example, in Sql Server 2017 you can use STRING_AGG:

select
    t.RequestId,
    concat('[',string_agg(t.CategoryId, ','),']') as Categories
from Table1 as t
group by
    t.RequestId

sql fiddle demo

If you need your array values to be strings instead of integers you can then you need to add " char manually and you also might want to use STRING_ESCAPE so special characters will be converted safely:

select
    t.RequestId,
    concat('[',string_agg(concat('"', string_escape(t.CategoryId, 'json'),'"'),','),']') as Categories
from Table1 as t
group by
    t.RequestId

sql fiddle demo

In yearlier versions of Sql Server you can either use xml trick or create custom clr aggregate.

cloudsafe · Answer 3 · 2018-07-24T08:22:57.047

1

Can be achieved using REPLACE:

 declare @test table ([Id] int, [RequestId] int, [CategoryId] int)
 insert into @test values
   (1, 112, 1)
 , (2, 123, 1)
 , (3, 123, 2)

    SELECT      R.RequestId,
                json_query(replace(replace((SELECT RC.CategoryId FROM @test RC WHERE RC.Id = R.Id FOR JSON AUTO), '{"CategoryId":', '"'), '}','"'))  AS Categories
    FROM        @test R

edited Jul 24 '18 at 08:22

answered Jul 24 '18 at 08:17

cloudsafe

2,444
1
8
24

score 0 · Answer 4 · answered Sep 27 '19 at 09:52

For sample table

SELECT Id,RequestId,CategoryId INTO #Request FROM ( values
( 1,      112,           '1'),
( 2,      123,           '1'),
( 3,      123,           '2'))v(Id,RequestId,CategoryId)
;

You can perform a recursive query

 WITH S AS (
    SELECT RequestId,CategoryId,ROW_NUMBER()OVER(Partition BY RequestId ORDER BY CategoryId DESC) _LP FROM  #Request
    )
    , C AS (
    SELECT s.RequestId,JSON_MODIFY('[]','append $',s.CategoryId)w,s._lp
    FROM (SELECT RequestId,MAX(_LP)_MLP FROM S GROUP BY RequestId) S1
    INNER JOIN S ON s.RequestId=s1.RequestId AND s._LP=s1._MLP
    union ALL
    SELECT s.RequestId,JSON_MODIFY(c.w,'append $',s.CategoryId)w,s._lp
    FROM  S
    inner join C ON s.RequestId=c.RequestId and s._LP=c._lp-1
    )
    SELECT RequestId,W Categories FROM C WHERE _LP=1

You will receive the result:

RequestId   Categories
----------- --------------------
112         ["1"]
123         ["1","2"]

Sql Query to get an array having no column name in json result

4 Answers4

Linked

Related