FOR JSON PATH – 生成数字数组,而非对象

huangapple go评论95阅读模式
英文:

FOR JSON PATH - produce array of numbers, not objects

问题

{
    "ids": ["1", "2", "3"]
}
英文:

I have a SQL Server table with just one column which I am trying to convert to a .json file.

The table is formatted like below

id
1
2
3

The desired JSON output looks like this:

{
    "ids": [ "1", "2", "3" ]
}

The closest I have come is with the following code

SELECT
    id
FROM 
    table 
FOR JSON PATH, ROOT('ids')

Which returns

{
    "ids": [ { "id": "1" },
             { "id": "2" },
             { "id": "3" } 
           ]
}

Any help is appreciated

答案1

得分: 2

DECLARE @DataSource TABLE
(
[ID] INT
);

INSERT INTO @DataSource ([ID])
VALUES (1), (2), (3);

SELECT JSON_QUERY(CONCAT('[', STRING_AGG('"' + CAST(id AS VARCHAR) + '"', ','), ']')) AS ids
FROM @DataSource
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

英文:

Maybe something like this:

DECLARE @DataSource TABLE
(
	[ID] INT
);

INSERT INTO @DataSource ([ID])
VALUES (1), (2), (3);

SELECT JSON_QUERY(CONCAT('[', STRING_AGG('"' + CAST(id AS VARCHAR) + '"', ','), ']')) AS ids
FROM @DataSource
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

FOR JSON PATH – 生成数字数组,而非对象

huangapple
  • 本文由 发表于 2023年6月2日 11:30:13
  • 转载请务必保留本文链接:https://go.coder-hub.com/76386957.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定