When using FOR JSON clause how to also return text fields containing JSON as part of the JSON result?

Question

I have rows in my table that looks like this:

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}]

I want to return the table as JSON:

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

My JSON looks like this:

[{
    "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}]"
}]

As you see additional_tasks is still a string. I can't seem to figure out how to convert that field into JSON with the rest. I am expecting this result:

[{
    "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

Use json_query() to embed existing JSON into your JSON result set, e.g.:

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

Which yields the result:

[
  {
    "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
      }
    ]
  }
]