如何用MySQL 5.7.22 JSON函数提取嵌套JSON的顶层目录项

Hey there! Let's break down how to solve your JSON extraction problem in MySQL 5.7.22. First, a quick note: MySQL 5.7 doesn't have built-in functions to directly filter JSON objects/arrays by depth or nested level, but we can work around it using the JSON functions you do have (like JSON_EXTRACT, JSON_ARRAYAGG) with a little index trickery.

Let's Start with Context #

First, let's assume your sample JSON document looks something like this (matching your mention of id, slug, and a nested toc array):

{
  "id": 123,
  "slug": "example-post",
  "toc": [
    { "depth": 1, "title": "Intro", "children": [{"depth":2, "title":"Sub Intro"}] },
    { "depth": 1, "title": "Main Section", "children": [] },
    { "depth": 2, "title": "Out-of-Level Entry" }
  ]
}

And your goal is to extract id, slug, plus only the toc entries where depth = 1 (the top-level ones).

The Workaround Query #

Since MySQL 5.7 lacks JSON_TABLE (that's an 8.0+ feature), we'll generate a sequence of index numbers to iterate through the toc array, filter entries with depth = 1, then aggregate them back into an array. Here's the query:

SELECT 
  JSON_OBJECT(
    'id', JSON_UNQUOTE(JSON_EXTRACT(p.content, '$.id')),
    'slug', JSON_UNQUOTE(JSON_EXTRACT(p.content, '$.slug')),
    'top_level_toc', JSON_ARRAYAGG(t.toc_item)
  ) AS extracted_result
FROM your_table_name p
JOIN (
  -- Generate index numbers to traverse the toc array
  SELECT 
    @row := @row + 1 AS idx,
    JSON_EXTRACT(p.content, CONCAT('$.toc[', @row, ']')) AS toc_item
  FROM your_table_name p,
       (SELECT @row := -1) init_counter,
       -- Add more UNION ALL SELECT lines if your toc has more than 5 entries
       (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) number_sequence
  -- Stop when we hit a null (end of the toc array)
  WHERE JSON_EXTRACT(p.content, CONCAT('$.toc[', @row, ']')) IS NOT NULL
) t 
-- Filter only toc entries with depth = 1
WHERE JSON_UNQUOTE(JSON_EXTRACT(t.toc_item, '$.depth')) = 1
-- Group to aggregate the filtered toc entries back into an array
GROUP BY p.id, p.content;

How This Works #

1. Index Sequence: The subquery generates a sequence of numbers (0, 1, 2, ...) to act as indexes for the toc array. Adjust the number_sequence subquery to include enough numbers to cover the largest toc array in your data.
2. Extract & Filter: For each index, we pull the corresponding toc entry, then check if its depth value is 1.
3. Aggregate: We use JSON_ARRAYAGG to combine all the filtered top-level toc entries back into a single array, then wrap everything up with JSON_OBJECT to match your desired output structure.

If You Mean "Top-Level by JSON Structure" #

If you actually wanted entries that are directly in the toc array (not nested in children), regardless of the depth field, you can simplify the filter to just check that the entry exists in the top level of toc (the index-based extraction already handles this, since we're only pulling from $.toc[N]).

Key Limitation to Note #

MySQL 5.7's JSON capabilities are more limited than 8.0. If you can upgrade later, JSON_TABLE would make this query much cleaner and scalable, but for 5.7.22, this index-traversal method is the way to go.

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