我正在尝试从一个表结果集形成json:
create table testmalc(
appid int identity(1,1),
propertyid1 int ,
propertyid1val varchar(10) ,
propertyid2 int,
propertyid2val varchar(10) ,
)
insert into testmalc values(456,'t1',789,'t2')
insert into testmalc values(900,'t3',902,'t4')
需要以下期望的JSON结果:
{
"data": {
"record": [{
"id": appid,
"customFields": [{
"customfieldid": propertyid1 ,
"customfieldvalue": propertyid1val
},
{
"customfieldid": propertyid2 ,
"customfieldvalue": propertyid2val
}
]
},
{
"id": appid,
"customFields": [{
"customfieldid": propertyid1 ,
"customfieldvalue": propertyid1val
},
{
"customfieldid": propertyid2 ,
"customfieldvalue": propertyid2val
}
]
}
]
}
}
我正在尝试使用stuff
,但是没有得到想要的结果。现在正在尝试使用UnPivot
。
发布于 2019-03-01 17:09:55
如果您不能升级到SQL-Server2016以获得JSON支持,那么您应该尝试使用您所知道的任何应用程序/编程语言来解决这个问题。
只是为了好玩,我提供了一种可行的方法,但与其说是一种解决方案,不如说是一种黑客:
您的测试数据:
DECLARE @testmalc table (
appid int identity(1,1),
propertyid1 int ,
propertyid1val varchar(10) ,
propertyid2 int,
propertyid2val varchar(10)
);
insert into @testmalc values(456,'t1',789,'t2')
,(900,'t3',902,'t4');
--创建一个XML,这是最相似的结构,并将其作为NVARCHAR
字符串读取
DECLARE @intermediateXML NVARCHAR(MAX)=
(
SELECT t.appid AS id
,(
SELECT t2.propertyid1 AS [prop1/@customfieldid]
,t2.propertyid1val AS [prop1/@customfieldvalue]
,t2.propertyid2 AS [prop2/@customfieldid]
,t2.propertyid2val AS [prop2/@customfieldvalue]
FROM @testmalc t2
WHERE t2.appid=t.appid
FOR XML PATH('customFields'),TYPE
) AS [*]
FROM @testmalc t
GROUP BY t.appid
FOR XML PATH('row')
);
--现在是一堆替补
SET @intermediateXML=REPLACE(REPLACE(REPLACE(REPLACE(@intermediateXML,'=',':'),'/>','}'),'<prop1 ','{'),'<prop2 ','{');
SET @intermediateXML=REPLACE(REPLACE(REPLACE(REPLACE(@intermediateXML,'<customFields>','"customFields":['),'</customFields>',']'),'customfieldid','"customfieldid"'),'customfieldvalue',',"customfieldvalue"');
SET @intermediateXML=REPLACE(REPLACE(@intermediateXML,'<id>','"id":'),'</id>',',');
SET @intermediateXML=REPLACE(REPLACE(REPLACE(@intermediateXML,'<row>','{'),'</row>','}'),'}{','},{');
DECLARE @json NVARCHAR(MAX)=N'{"data":{"record":[' + @intermediateXML + ']}}';
PRINT @json;
结果(格式化)
{
"data": {
"record": [
{
"id": 1,
"customFields": [
{
"customfieldid": "456",
"customfieldvalue": "t1"
},
{
"customfieldid": "789",
"customfieldvalue": "t2"
}
]
},
{
"id": 2,
"customFields": [
{
"customfieldid": "900",
"customfieldvalue": "t3"
},
{
"customfieldid": "902",
"customfieldvalue": "t4"
}
]
}
]
}
}
https://stackoverflow.com/questions/54940328
复制相似问题