SQL查询以获取JSON结果中没有列名的数组 [英] Sql Query to get an array having no column name in json result

问题描述

我有一个包含以下字段的表格

 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

上面的查询返回的数据如下所述

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

但是，我希望列名 CategoryId 不应对json数组中的每个项目重复.因此，我的预期结果是:

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

解决方案

使用情况:

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<>小提琴此处

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"]

解决方案

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

这篇关于SQL查询以获取JSON结果中没有列名的数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！