使用FOR JSON子句时，如何还返回包含JSON的文本字段作为JSON结果的一部分?

Question

我的表中有这样的行:

template_id  template_name  default_job_name  additional_tasks
1            Indiana Courts Hearing           [{"extra_task_id":3,"task_name":"Project Monitoring","task_type":12,"task_notes":"","client_price":null,"ic_cost":null},{"extra_task_id":4,"task_name":"Do Some stuff","task_type":9,"task_notes":"Note","client_price":12,"ic_cost":6}]

我想返回JSON格式的表:

select template_id, template_name, default_job_name, additional_tasks 
from templates_realtime
FOR JSON PATH

我的JSON是这样的:

[{
"template_id": 1,
"template_name": "Indiana Courts",
"default_job_name": "Hearing",
"additional_tasks": "[{"extra_task_id":3,"task_name":"Project Monitoring","task_type":12,"task_notes":"","client_price":null,"ic_cost":null},{"extra_task_id":4,"task_name":"Do Some stuff","task_type":9,"task_notes":"Note","client_price":12,"ic_cost":6}]"
}]

如你所见，additional_tasks仍然是一个字符串。我似乎无法弄清楚如何将该字段与其他字段转换为JSON。我期待这个结果:

[{
"template_id": 1,
"template_name": "Indiana Courts",
"default_job_name": "Hearing",
"additional_tasks": [{
"extra_task_id": 3,
"task_name": "Project Monitoring",
"task_type": 12,
"task_notes": "",
"client_price": null,
"ic_cost": null
}, {
"extra_task_id": 4,
"task_name": "Do Some stuff",
"task_type": 9,
"task_notes": "Note",
"client_price": 12,
"ic_cost": 6
}]
}]

Answer 1

使用json_query()将现有JSON嵌入到JSON结果集中，例如:

select
template_id,
template_name,
default_job_name,
json_query(additional_tasks) as additional_tasks
from templates_realtime
for json path;

生成结果:

[
{
"template_id": 1,
"template_name": "Indiana Courts",
"default_job_name": "Hearing",
"additional_tasks": [
{
"extra_task_id": 3,
"task_name": "Project Monitoring",
"task_type": 12,
"task_notes": "",
"client_price": null,
"ic_cost": null
},
{
"extra_task_id": 4,
"task_name": "Do Some stuff",
"task_type": 9,
"task_notes": "Note",
"client_price": 12,
"ic_cost": 6
}
]
}
]