英文:
SQL OPENJSON Array in Objects
问题
以下是您需要的翻译：
I have a table with the following structure/rows:
| ID | OptionName | OptionValue |
|---|---|---|
| 0 | Gender | Male |
| 1 | Gender | Female |
我有一个具有以下结构/行的表格：
| ID | OptionName | OptionValue |
|---|---|---|
| 0 | Gender | Male |
| 1 | Gender | Female |
I want to query the database and return the following JSON:
我想查询数据库并返回以下JSON：
[{
"OptionName":"Gender",
"Values":[
"Male",
"Female"
]
}]
我想查询数据库并返回以下JSON：
[{"OptionName":"Gender","Values":["Male","Female"]}]
However the result I'm currently getting is this:
但是我目前得到的结果是这样的：
[{"OptionName":"Gender","Values":[{"OptionValue":"Male"},{"OptionValue":"Female"}]}]
Here is my Query:
这是我的查询：
SELECT TOP(1) OptionName,(JSON_QUERY((SELECT OptionValueFROM [TestJSON].[dbo].[Options]WHERE OptionName = 'Gender'FOR JSON PATH))) AS [Values]FROM [TestJSON].[dbo].[Options]WHERE OptionName = 'Gender'FOR JSON PATH
这是我的查询：
SELECT TOP(1) OptionName,(JSON_QUERY((SELECT OptionValueFROM [TestJSON].[dbo].[Options]WHERE OptionName = 'Gender'FOR JSON PATH))) AS [Values]FROM [TestJSON].[dbo].[Options]WHERE OptionName = 'Gender'FOR JSON PATH
What can I do to get the result I need?
我应该怎么做才能获得我需要的结果？
英文:
I have a table with the following structure/rows:
| ID | OptionName | OptionValue |
|---|---|---|
| 0 | Gender | Male |
| 1 | Gender | Female |
I want to query the database and return the following JSON:
[{"OptionName":"Gender","Values":["Male","Female"]}]
However the result I'm currently getting is this:
[{"OptionName":"Gender","Values":[{"OptionValue":"Male"},{"OptionValue":"Female"}]}]
Here is my Query:
SELECT TOP(1) OptionName,(JSON_QUERY((SELECT OptionValueFROM [TestJSON].[dbo].[Options]WHERE OptionName = 'Gender'FOR JSON PATH))) AS [Values]FROM [TestJSON].[dbo].[Options]WHERE OptionName = 'Gender'FOR JSON PATH
What can I do to get the result I need?
答案1
得分: 1
虽然 SQL Server 2022 引入了 JSON_ARRAY() 函数，但要使用它构建具有可变项目数量的 JSON 数组比较困难，因此您可以尝试基于字符串的方法：
SELECT DISTINCT o.OptionName, JSON_QUERY(a.[Values]) AS [Values]FROM Options oCROSS APPLY (SELECT CONCAT('[', STRING_AGG(CONCAT('"', OptionValue, '"'), ','), ']')FROM OptionsWHERE OptionName = o.OptionName) a ([Values])--WHERE o.OptionName = 'Gender'FOR JSON PATH
如果您知道每个选项的最大值数量（例如示例中的 5），还有另一种选择，可以使用 JSON_ARRAY() 和 PIVOT 关系运算符的组合：
SELECT OptionName, JSON_ARRAY([1], [2], [3], [4], [5]) AS [Values]FROM (SELECT OptionName, OptionValue, ROW_NUMBER() OVER (PARTITION BY OptionName ORDER BY ID) AS RnFROM Options) tPIVOT (MAX(OptionValue) FOR Rn IN ([1], [2], [3], [4], [5])) pFOR JSON PATH
英文:
Although SQL Server 2022 introduced JSON_ARRAY() function, it's difficult to use it to build a JSON array with variable items count, so you may try a string based approach:
SELECT DISTINCT o.OptionName, JSON_QUERY(a.[Values]) AS [Values]FROM Options oCROSS APPLY (SELECT CONCAT('[', STRING_AGG(CONCAT('"', OptionValue, '"'), ','), ']')FROM OptionsWHERE OptionName = o.OptionName) a ([Values])--WHERE o.OptionName = 'Gender'FOR JSON PATH
If you know the maximum number of values for each option (5 in the example), a combination of JSON_ARRAY() and PIVOT relational operator is another option:
SELECT OptionName, JSON_ARRAY([1], [2], [3], [4], [5]) AS [Values]FROM (SELECT OptionName, OptionValue, ROW_NUMBER() OVER (PARTITION BY OptionName ORDER BY ID) AS RnFROM Options) tPIVOT (MAX(OptionValue) FOR Rn IN ([1], [2], [3], [4], [5])) pFOR JSON PATH
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论