json - 对于 JSON 路径格式
问题描述
背景:
我有一个nvarchar(max)
名为“问题”的 JSON 列,它看起来像单行中的这个真实示例......
{"211":0,"212":0,"213":0,"214":0,"215":0,"216":0,"217":0,"218":0,"219":0,"220":"1","221":"1","222":"1","223":"1","224":"1","225":"1","226":"1","227":"1","228":"1","229":"1","230":"1","231":"1","232":"1"}
我目前正在为示例“调用”生成此示例 JSON 片段...
[
{
"call": {
"id": 200643,
"yes_answers": [
{
"question_id": "220"
},
{
"question_id": "221"
},
{
"question_id": "222"
},
{
"question_id": "223"
},
{
"question_id": "224"
},
{
"question_id": "225"
},
{
"question_id": "226"
},
{
"question_id": "227"
},
{
"question_id": "228"
},
{
"question_id": "229"
},
{
"question_id": "230"
},
{
"question_id": "231"
},
{
"question_id": "232"
}
]
}
}
]
..使用这个查询...
select c.call_id as [call.id],
(
select x.[key]
from [call].[triage_questions] tq
cross apply openjson(questions, '$') as x
where value = 1 and tq.call_id = c.call_id
for json path
) as [call.yes_answers]
from [call].[dimension] c
where call_id = 200643
for json path
我的问题:
我不喜欢“yes_answers”数组的格式化方式。我想要更像这样的东西:
[
{
"call": {
"id": 200643,
"yes_answers": [
220,
221,
222,
223,
224,
225,
226,
227,
228,
229,
230,
231,
232
]
}
}
]
这是有效的吗?还是我应该以不同的方式展开?还是离开它?
我将使用 SQL 将“yes_answers”数组中的每个 question_id 引用到一个引用表,在该表中我正在寻找与该问题关联的真/假标志。如果这很重要,那么此时 JSON 不会离开 SQL。
编辑:
感谢@Tomato32,我想我发现了另一个帮助我足够接近的问题。
我的查询现在看起来像这样......
select c.call_id as [call.id],
json_query(replace(replace((
select x.[key] as question_id
from [call].[triage_questions] tq
cross apply openjson(questions, '$') as x
where value = 1 and tq.call_id = c.call_id
order by x.[key]
for json path
), N'{"question_id":', N''), '"}', '"')) as [call.yes_answers]
from [call].[dimension] c
where call_id = 200643
for json path
我生成的 JSON 看起来像这样......
[
{
"call": {
"id": 200643,
"yes_answers": [
"220",
"221",
"222",
"223",
"224",
"225",
"226",
"227",
"228",
"229",
"230",
"231",
"232"
]
}
}
]
我无法摆脱“yes_answers”数组中 int 值周围的引号,但我认为这无关紧要,我不会浪费任何时间 :) 谢谢大家! !
解决方案
我只更新了您的替换语句以删除“[number]”中的引号,如下所示:-
select c.call_id as [call.id],
json_query(replace(replace((
select x.[key] as question_id
from [call].[triage_questions] tq
cross apply openjson(questions, '$') as x
where value = 1 and tq.call_id = c.call_id
order by x.[key]
for json path
), N'{"question_id":"', N''), '"}', '')) as [call.yes_answers]
from [call].[dimension] c
where call_id = 200643
for json path
在我的测试中,输出如下:-
[{"call":{"id":200643,"yes_answers":[220,221,222,223,224,225,226,227,228,229,230,231,232]}}]
推荐阅读
- mysql - 无法打开 /proc/net/unix:没有这样的文件或目录 //invoke-rc.d:无法确定当前运行级别
- azure - 跳过 Azure Pipelines 发布 XML 变量替换
- java - java - 如何使用实体管理器从java中的映射实体中过滤
- javascript - 如果 3 列中的任何日期 = 今天,Google Sheets Appscript 发送电子邮件
- compression - 可以从自制软件中获得压缩吗?
- bash - 跳过 sed 中的模式
- javascript - “DateTimeFormat”类型上不存在属性“formatToParts”
- java - 无法在我的类中使用 onBindViewHolder 实现扩展 RecyclerView.Adapter
- centos - 如何删除:rpmdb failed release 提供
- python - 你如何在 PYplot 中设置起点?