如何通过SQL查询从自引用表返回JSON树形结构？

表结构说明 #

• 自引用表jccontent包含字段：content_id、root_id、parent_id、content_level
• 子记录通过parent_id关联父记录，父记录不存储子记录关联信息
• 根记录特征：content_level=0，root_id和parent_id均为NULL；其余记录的root_id与对应根记录的content_id一致

当前查询SQL #

用于获取根内容（content_id=138412032）及其所有子记录：

SELECT *
FROM jccontent c2 
WHERE c2.content_id = 138412032
UNION ALL
(
    SELECT j.*
    FROM jccontent AS c 
    INNER JOIN jccontent j on c.content_id = j.parent_id 
    WHERE j.root_id  = 138412032 
)
ORDER BY content_level ;

需求目标 #

仅通过SQL构建以根元素为顶层、包含嵌套子元素的JSON树形结构，示例输出如下：

{
  "content_id": 138412032,
  "root_id": null,
  "parent_id": null,
  "content_level": 0,
  "children": [
    {
      "content_id": 1572864000,
      "root_id": 138412032,
      "parent_id": 138412032,
      "content_level": 1,
      "children": [
        {
          "content_id": 1606418432,
          "root_id": 138412032,
          "parent_id": 1572864000,
          "content_level": 2,
          "children": []
        },
        {
          "content_id": 515899393,
          "root_id": 138412032,
          "parent_id": 1572864000,
          "content_level": 2,
          "children": [
            {
              "content_id": 75497471,
              "root_id": 138412032,
              "parent_id": 515899393,
              "content_level": 3,
              "children": []
            }
          ]
        }
      ]
    },
    {
      "content_id": 1795162113,
      "root_id": 138412032,
      "parent_id": 138412032,
      "content_level": 1,
      "children": []
    }
  ]
}

补充说明 #

目前已通过代码实现该功能且运行稳定，想了解通过SQL实现是否更优，若需补充信息可随时提供。

内容的提问来源于stack exchange，提问作者vincentyeung