How to remove a tuple from an array by the value of the tuple?

Question

MySQL

There is a following line in the table

+---+----------------------+
| id | sort
+---+----------------------+
| 1 | [12, 13, {"12": 123} ]
+---+----------------------+

How can I remove the element {"12": 123} without knowing its index in the array, but knowing the tuple itself ?

Answer 1

Divide into elements, discard unnecessary, collect back:

WITH cte AS (SELECT 1 id, CAST('[12, 13, {"12": 123} ]' AS JSON) sort)
SELECT cte.id, JSON_ARRAYAGG(jsontable.element) sort
FROM cte
CROSS JOIN JSON_TABLE(cte.sort,
                      '$[*]' COLUMNS (element JSON PATH '$')) jsontable
WHERE jsontable.element != CAST('{"12": 123}' AS JSON)
GROUP BY cte.id

https://dbfiddle.uk/?rdbms= mysql_8.0 &fiddle=405ff0... Comparing JSON objects is a thankless task. In more complex cases, it will not work, even JSON_OVERLAPS () may not save. Perhaps using string functions on a JSON string representation is a better solution.