Filtering mysql data using nested json_table returns "Incorrect arguments"

I'm new to mysql and I'm trying to filter some data. I needed more entires to be in one column, so I used the json format.

With the help of chatgpt I'm able to search for fine, but there's a problem with operators.

I'd like to use AND, OR and NOT in one query, but that doesn't seem to work. So I figured I could firstly filter out using AND, then OR and finally NOT. So that brings me to nested json_tables.

But firstly, this is what the old query looks like:

SELECT id, attributes FROM files,
JSON_TABLE(attributes, '$[*]' COLUMNS(value TEXT PATH '$')) AS jt
WHERE jt.value LIKE "%a%" OR jt.value LIKE "%f%"

With the help of chatgpt this is what the not working query looks like:

SELECT id, attributes
FROM (
    SELECT id, attributes FROM files,
    JSON_TABLE(attributes, '$[*]' COLUMNS(value TEXT PATH '$')) AS jt
    WHERE jt.value LIKE "%a%" OR jt.value LIKE "%f%"
) AS or_filtered,
JSON_TABLE(or_filtered.attributes, '$[*]' COLUMNS(value TEXT PATH '$')) AS jt2 WHERE jt2.value LIKE "sa";

That returns ERROR 1210 (HY000): Incorrect arguments to JSON_TABLE.

While browsing I came across a different solution, but that still doesn't seem to work. It used CROSS JOIN (is my syntax incorrect?):

WITH or_filtered AS (
    SELECT id, attributes FROM files,
    JSON_TABLE(attributes, '$[*]' COLUMNS(value TEXT PATH '$')) AS jt
    WHERE jt.value LIKE "%a%" OR jt.value LIKE "%f%"
)
SELECT * FROM or_filtered CROSS JOIN JSON_TABLE(or_filtered.attributes, '$[*]' COLUMNS(value TEXT PATH '$')) AS jt2 WHERE jt2.value LIKE "sa";

I'm not sure why json_table doesn't want to accept data from the or_filtered table. I also did try just using attributes instead of or_filtered.attributes, but it's still the same error.

Solution

This is a MySQL bug, reported in 2022 and verified as a bug, but not yet fixed. See https://bugs.mysql.com/bug.php?id=108365

When you use a derived table subquery, and the optimizer feature of derived_merge is enabled, it confuses JSON_TABLE(), so it doesn't see the column as JSON type.

Any of the following four workarounds allows the query to run without error:

Turning off the optimizer feature that causes the problem.
```
 set optimizer_switch="derived_merge=off";
```

Using LIMIT in the subquery. This apparently blocks the optimizer feature.

 SELECT id, attributes, jt2.value
 FROM (
     SELECT id, attributes FROM files,
     JSON_TABLE(attributes, '$[*]' COLUMNS(value TEXT PATH '$')) AS jt
     WHERE jt.value LIKE "%a%" OR jt.value LIKE "%f%"
     LIMIT 100
 ) AS or_filtered
 CROSS JOIN
 JSON_TABLE(attributes, '$[*]' COLUMNS(value TEXT PATH '$')) AS jt2
 WHERE jt2.value LIKE "sa";

Explicitly casting the column to JSON.

 SELECT id, attributes, jt2.value
 FROM (
     SELECT id, attributes FROM files,
     JSON_TABLE(attributes, '$[*]' COLUMNS(value TEXT PATH '$')) AS jt
     WHERE jt.value LIKE "%a%" OR jt.value LIKE "%f%"
 ) AS or_filtered
 CROSS JOIN
 JSON_TABLE(CAST(attributes AS JSON), '$[*]' COLUMNS(value TEXT PATH '$')) AS jt2
 WHERE jt2.value LIKE "sa";

Using another trivial JSON expression that coerces the result to JSON type.

 SELECT id, attributes, jt2.value
 FROM (
     SELECT id, attributes FROM files,
     JSON_TABLE(attributes, '$[*]' COLUMNS(value TEXT PATH '$')) AS jt
     WHERE jt.value LIKE "%a%" OR jt.value LIKE "%f%"
 ) AS or_filtered
 CROSS JOIN
 JSON_TABLE(attributes->'$', '$[*]' COLUMNS(value TEXT PATH '$')) AS jt2
 WHERE jt2.value LIKE "sa";