我的最终目标是建立一个 JSON_OBJECT() select语句只从存储的json值中收集几个值。
select语句的当前迭代：

SELECT
  JSON_OBJECT(
      'id', document->>'$.id',
      'slug', document->>'$.slug',
      'title', document->>'$.title',
      'date_published', document->>'$.date_published',
      'toc', (SELECT JSON_OBJECT('title', document->>'$.toc[*][*].title') WHERE document->>'$.id' = document->>'$.id' )
  ) as document
FROM documents
WHERE 
  `type` = "item" AND
  NOW() > date_published;

举个例子 item 文档可能是：

{
  "id": "d434089d-33ac-11e8-af1c-005056a40c60",
  "toc": [
    [
      {
        "id": "d4e1e442-57a9-11e8-af1c-005056a40c60",
        "slug": "0-intro",
        "chunk": 0,
        "index": 0,
        "pages": [],
        "title": "Intro",
        "urlsuffix": ""
      },
      {
        "id": "d4e1ef07-57a9-11e8-af1c-005056a40c60",
        "slug": "1-beginning",
        "chunk": 0,
        "index": 1,
        "pages": [
          {
            "id": "d4e1f726-57a9-11e8-af1c-005056a40c60",
            "slug": "2-nested-example",
            "chunk": 0,
            "index": 2,
            "pages": [],
            "title": "Nested Example",
            "urlsuffix": ""
          },
          {
            "id": "d4e1fee4-57a9-11e8-af1c-005056a40c60",
            "slug": "3-three-layers-deep",
            "chunk": 0,
            "index": 3,
            "pages": [
              {
                "id": "d4e2065e-57a9-11e8-af1c-005056a40c60",
                "slug": "4-four-in",
                "chunk": 0,
                "index": 4,
                "pages": [],
                "title": "Four In",
                "urlsuffix": ""
              },
              {
                "id": "d4e20e47-57a9-11e8-af1c-005056a40c60",
                "slug": "5-maximum-nesting",
                "chunk": 0,
                "index": 5,
                "pages": [],
                "title": "This is likely the maximum amount of Nesting",
                "urlsuffix": ""
              }
            ],
            "title": "Three Layers Deep",
            "urlsuffix": ""
          },
          {
            "id": "d4e2168b-57a9-11e8-af1c-005056a40c60",
            "slug": "6-follow-up-chapter",
            "chunk": 0,
            "index": 6,
            "pages": [],
            "title": "Follow-up Chapter",
            "urlsuffix": ""
          }
        ],
        "title": "The Beginning",
        "urlsuffix": ""
      }
    ]
  ],
  "slug": "an-item",
  "tags": [
    "test-item"
  ],
  "type": "item",
  "title": "An Item",
  "download": null,
  "date_added": "1522342602",
  "page_count": null,
  "description": "",
  "date_published": "1522238400"
}

这就是我试图作为select语句的结果记录（例如示例文档）实现的目标：

{
  "id": "d434089d-33ac-11e8-af1c-005056a40c60",
  "slug": "an-item",
  "title": "An Item",
  "date_published": "1522238400",
  "toc": [
    {
      "id": "d4e1e442-57a9-11e8-af1c-005056a40c60",
      "slug": "0-intro",
      "title": "Intro",
      "urlsuffix": ""
    },
    {
      "id": "d4e1ef07-57a9-11e8-af1c-005056a40c60",
      "slug": "1-beginning",
      "title": "The Beginning",
      "urlsuffix": ""
    }
  ]
}

所以基本上，只需要顶级目录项的数组（depth[1]）
如果我必须的话，我会同意在应用程序中完成逻辑，我只是觉得有一种方法可以使用mysql json函数来实现这一点。
在选择对象/阵列时，是否有方法要求最大深度？
我使用的是5.7.22，所以我有最新的json特性（json_arrayagg/json_objectagg等）