mysqljsonnestedjson-table

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:

    1. Turning off the optimizer feature that causes the problem.

       set optimizer_switch="derived_merge=off";
      
    2. 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";
      
    3. 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";
      
    4. 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";