使用 FOR JSON 时获取值数组而不是对象数组

Question

我正在尝试扁平化由FOR JSON构造的对象数组。

我的查询如下所示：

select 
(                           
select id from MyTable
where id in (select value from OPENJSON(@jsonArray))
FOR JSON PATH
) existing,                 
(   
select value id from OPENJSON(@jsonArray) 
where value not in (select Id from MyTable)
FOR JSON PATH                       
) missing
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

生成的 JSON 为：

{
"existing": [
{
"id": "a00cd8f6-d1c6-4604-b235-59d3cacd5bcc"
},
{
"id": "052455b6-6bf5-47d3-8bee-7ba98d7fbd50"
}
],
"missing": [
{
"id": "328add2d-e8f2-4a0e-af54-5b1733310170"
}
]
}

相反，我想要的是：

{
"existing": [
{
"id": "a00cd8f6-d1c6-4604-b235-59d3cacd5bcc"
},
{
"id": "052455b6-6bf5-47d3-8bee-7ba98d7fbd50"
}
],
"missing": [
"328add2d-e8f2-4a0e-af54-5b1733310170"            
]
}

缺少的数组不应包含 json 对象，而应包含值。 有什么建议吗？

Answer 1

如果您使用的是SQL Server 2017，则可以使用JSON_QUERY和STRING_AGG构建数组(使用SQL Server 2016，您不能使用STRING_AGG，因此您必须做一些estra工作，但以下想法仍然有效(：

declare @missing table(id varchar(max))
declare @existing table(id varchar(max))
insert into @missing values ('a00cd8f6-d1c6-4604-b235-59d3cacd5bcc')
insert into @missing values ('052455b6-6bf5-47d3-8bee-7ba98d7fbd50')
insert into @existing values ('328add2d-e8f2-4a0e-af54-5b1733310170')
select  
(                           
select id from @missing
FOR JSON PATH
) existing,                
(   
select JSON_QUERY(concat('[' , STRING_AGG(concat('"' , STRING_ESCAPE(id, 'json') , '"'),',') , ']')) 
from @existing                 
) missing 
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

结果：

{
"existing": [
{
"id": "a00cd8f6-d1c6-4604-b235-59d3cacd5bcc"
},
{
"id": "052455b6-6bf5-47d3-8bee-7ba98d7fbd50"
}
],
"missing": [
"328add2d-e8f2-4a0e-af54-5b1733310170"
]
}

Answer 2

这并不像应该的那么容易...

AFAIK 没有办法使用 sql 服务器创建裸json 数组。但是你可以在字符串级别上解决这个问题：

DECLARE @exist TABLE(id VARCHAR(100));
DECLARE @miss TABLE(id VARCHAR(100));
INSERT INTO @exist VALUES ('exist1'),('exist2');
INSERT INTO @miss VALUES ('miss1'),('miss2');

--这将创建你想要的对象数组

SELECT id FROM @exist
FOR JSON PATH

--这将使用一些相当丑陋的技巧创建裸数组。

SELECT REPLACE(REPLACE(REPLACE(
(
SELECT id from @miss
FOR JSON PATH
),'"id":',''),'{',''),'}','')

--现在我们必须将两者结合起来。我们再次需要一个技巧。我们在 JSON 文本上使用JSON_QUERY()以避免转义引号。

SELECT
(
SELECT id FROM @exist
FOR JSON PATH
) AS existing
,JSON_QUERY(
REPLACE(REPLACE(REPLACE(
(
SELECT id from @miss
FOR JSON PATH
),'"id":',''),'{',''),'}','')
) AS missing
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

这是结果

{
"existing":[{"id":"exist1"},{"id":"exist2"}]  <--array of objects
,"missing":["miss1","miss2"]                   <--array of naked values
}

我不知道，为什么这不能开箱即用......

Answer 3

使用从游标追加

DECLARE @missing nvarchar(max),
@json nvarchar(max) = (select 
(                           
select id from MyTable
where id in (select value from OPENJSON(@jsonArray))
FOR JSON PATH
) existing
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
DECLARE missing_cursor CURSOR FOR   
select value id 
from OPENJSON(@jsonArray)
where value not in (select Id from MyTable)
OPEN missing_cursor  
FETCH NEXT FROM missing_cursor   
INTO @missing
WHILE @@FETCH_STATUS = 0  
BEGIN  
SET @json = JSON_MODIFY(@json,'append $.missing', @missing)
FETCH NEXT FROM missing_cursor   
INTO @missing  
END   
CLOSE missing_cursor;  
DEALLOCATE missing_cursor; 
select @json