英文:
Generate JSON key Value dictionary using SQL Server with multiple rows
问题
尝试找出将多行数据转换为单行KeyValue数组的方法。
原始数据：
| id | FkId | name | value |
|---|---|---|---|
| 83713 | 1024180 | Name1 | Value1 |
| 83714 | 1024180 | Name2 | Value2 |
期望结果：
{ "Name1":"Value1", "Name2":"Value2" }
我尝试了不同的组合，使用Json Path和JSON_Array，但都没有得到我需要的结果。
SELECT JSON_ARRAY(s.name,s.value) AS result
FROM tbl s
WHERE FkId = 1024180
结果是两行带有包装的数组：
row 0 ["Name1","Value1"],
row 1 ["Name2","Value2"]
尝试：
SELECT s.name, s.value
FROM tbl s
WHERE fkid= 1024180
FOR JSON PATH
结果：
[{"name":"SpaceSubType","value":"Fee Earner"},{"name":"Department","value":"Property"}]
是否有合理的方法可以得到我想要的输出？
结果
根据Yitzhak的建议，我已经非常接近我需要的结果，但是Json Path现在转义了手动生成的字典。有没有办法去除这些转义字符？
格式化
select
sa.Id as 'id',
s.id as 'Sid',
(SELECT ('{' + STRING_AGG('"' + sp.name + '": "' + sp.value + '"', ',') + '}') as barf FROM tbl sp where foo = s.id) as properties
from TblA sa
inner join TblB s on s.id = sa.Sid
Where sa.Sid = '04000772'
For Json path,
INCLUDE_NULL_VALUES
结果
[
{
"id": "04000772",
"Sid": 1024180,
"Properties": "{\"Name1\": \"Value1\",\"Name2\": \"Value2\"}"
}
]
根据下面的评论，这是构建字符串后进行转义的方法。
…s.id as Sid, json_query((select '{'+string_agg('"' + string_escape(name, 'json') + '":"'+isnull(string_escape(value, 'json'),'null')+'"', ',')+'}' from tableX)) as properties
英文:
Trying to figure out how to convert multiple rows of data to a single row of KeyValue array.
Raw data:
| id | FkId | name | value |
|---|---|---|---|
| 83713 | 1024180 | Name1 | Value1 |
| 83714 | 1024180 | Name2 | Value2 |
Desired result:
{ "Name1":"Value1", "Name2":"Value2" }
I have tried different combos using Json Path and JSON_Array.. neither are resolving to what I need.
SELECT JSON_ARRAY(s.name,s.value) AS result
FROM tbl s
WHERE FkId = 1024180
Result is two rows with wrappers:
row 0 ["Name1","Value1"],
row 1 ["Name2","Value2"]
Tried:
SELECT s.name, s.value
FROM tbl s
WHERE fkid= 1024180
FOR JSON PATH
Result:
[{"name":"SpaceSubType","value":"Fee Earner"},{"name":"Department","value":"Property"}]
Is there a reasonable way to get the output I am trying for?
results
Applying Yitzhak's recommendation I am super close to what I need however the json path is now escaping the manually generated dictionary. Any idea if possible to somehow trim those escape chars out?
formatted
select
sa.Id as 'id',
s.id as 'Sid',
(SELECT ('{' + STRING_AGG('"' + sp.name + '": "' + sp.value + '"', ',') + '}') as barf FROM tbl sp where foo = s.id) as properties
from TblA sa
inner join TblB s on s.id = sa.Sid
Where sa.Sid = '04000772'
For Json path,
INCLUDE_NULL_VALUES
result
[
{
"id": "04000772",
"Sid": 1024180,
"Properties": "{\"Name1\": \"Value1\",\"Name2\": \"Value2\"}"
}
]
as per comments below.. this is the ticket to escaping the string once built.
…s.id as Sid, json_query((select '{'+string_agg('"'+string_escape(name, 'json') +'":'+isnull('"'+string_escape(value, 'json')+'"', 'null'), ',')+'}' from tableX)) as properties
答案1
得分: 1
请尝试以下解决方案。
从SQL Server 2017开始，它将起作用。
SQL
-- DDL和示例数据填充，开始
DECLARE @tbl TABLE (id INT PRIMARY KEY, FkId INT, name VARCHAR(20), value VARCHAR(20));
INSERT @tbl (id, FkId, name, value) VALUES
(83713, '1024180', 'Name1', 'Value1'),
(83714, '1024180', 'Name2', 'Value2');
-- DDL和示例数据填充，结束
SELECT '{' + STRING_AGG('"' + name + '": "' + value + '"', ',') + '}'
FROM @tbl
WHERE fkid = 1024180
Output
{
"Name1": "Value1",
"Name2": "Value2"
}
英文:
Please try the following solution.
It will work starting from SQL Server 2017 onwards.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT PRIMARY KEY, FkId INT, name VARCHAR(20), value VARCHAR(20));
INSERT @tbl (id, FkId, name, value) VALUES
(83713, '1024180', 'Name1', 'Value1'),
(83714, '1024180', 'Name2', 'Value2');
-- DDL and sample data population, end
SELECT '{' + STRING_AGG('"' + name + '": "' + value + '"', ',') + '}'
FROM @tbl
WHERE fkid = 1024180
Output
{
"Name1": "Value1",
"Name2": "Value2"
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论