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.
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";