sql - 如何在 SQL 中创建单个对象 JSON 数组?
问题描述
我正在尝试将一行数据转换为包含单个对象的 JSON 数组。一列包含 XML,它概述了另一个转换为数组的单个对象。
我的查询:
WITH r AS (
SELECT TOP 1 * FROM Table1
ORDER BY RecordID ASC)
SELECT
NEWID() AS 'Report.ReportUUID',
Value1 as 'Report.Value1',
Value2 as 'Report.Value2',
DateTime as 'Report.DateTime',
UserID as 'Report.UserID',
'Medium' as 'Report.Priority',
NEWID() as 'Report.Item.ItemUUID',
XML.value('category[1]', 'varchar(100)') as 'Report.Item.Category',
XML.value('description[1]', 'varchar(1000)') as 'Report.Item.Description',
XML.value('date[1]', 'varchar(100)') AS 'Report.Item.DateTime'
FROM r
FOR JSON PATH, ROOT('DataSet');
期望的输出:
{
"DataSet" : {
"Report" : [
{
"ReportUUID" : "uuid here",
"Value1" : "value1",
"Value2" : "value2",
"DateTime" : "2020-04-06 16:00:00",
"UserID" : "1234",
"Priority" : "Medium",
"Item" : [
{
"ItemUUID" : "uuid here",
"Category" : "01",
"Description" : "Desc",
"DateTime" : "2020-04-05 08:00:00"
}
]
}
]
}
}
实际输出:
{
"DataSet": [
{
"Report": {
"ReportUUID" : "uuid here",
"Value1": "value1",
"Value2": "value2",
"DateTime": "2020-04-06 16:00:00",
"UserID": "1234",
"Priority": "Medium",
"Item": {
"ItemUUID": "uuid here",
"Category": "01",
"Description": "Desc",
"DateTime": "2020-04-05 08:00:00"
}
}
}
]
}
DataSet
必须是单个对象,Report
并且Item
必须是包含单个对象的数组。任何人都可以帮助格式化这样的数据吗?
解决方案
您需要如下声明:
;WITH r AS (
SELECT TOP 1 * FROM Table1
ORDER BY RecordID ASC
)
SELECT Report AS 'DataSet.Report'
FROM (
SELECT
NEWID() AS 'ReportUUID',
Value1 as 'Value1',
Value2 as 'Value2',
DateTime as 'DateTime',
UserID as 'UserID',
'Medium' as 'Priority',
(
SELECT
NEWID() as 'ItemUUID',
XML.value('category[1]', 'varchar(100)') as 'Category',
XML.value('description[1]', 'varchar(1000)') as 'Description',
XML.value('date[1]', 'varchar(100)') AS 'DateTime'
FOR JSON PATH
) AS 'Item'
FROM r
FOR JSON AUTO
) t (Report)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
推荐阅读
- java - 将变量从 Dialog 传递到 Listview 片段
- extjs - 在 BorderLayout 中心面板上设置 minWidth?
- design-patterns - 分离策略模式返回的类型
- ios - 内容不适合时的 UIView 多行 [iOS]
- javascript - 为什么我的代码会抛出错误“原型不存在”?
- ruby-on-rails - 如何提交多个下拉列表以及其他参数?
- r - 如何从一系列字符中选择唯一的字符串
- python - ImportError:没有名为 nltk.classify 的模块
- android - 认证成功后如何完成活动
- r - R: map_dfr + read_table() 中的编码选项