MySQL, JSON_CONTAINS usage on array

Question

How to use 'JSON_CONTAINS' by such structure in MySQL JSON field? [{"uuid": "a07b50ca-42f0-4d2b-b0a2-f2980deb03d8"}] Doing this way:

SELECT * from tables c where 
    JSON_CONTAINS(c.data, '64a3104c-01e6-417b-8a11-bf5af73ad87d', '$[*].uuid') 

but getting error

Invalid JSON text in argument 1 to function json_contains: 
"The document root must not be followed by other values." at position 2.

Answer 1

The documentation for JSON_CONTAINS() says:

An error occurs if target or candidate is not a valid JSON document, or if the path argument is not a valid path expression or contains a * or ** wildcard.

This means you can't use JSON_CONTAINS() unless you're searching for a value at a specific path.

Also the second argument must be a JSON document, not a string.

mysql> SELECT * from tables c where
       JSON_CONTAINS(c.data, '"a07b50ca-42f0-4d2b-b0a2-f2980deb03d8"', '$[0].uuid');
+----+----------------------------------------------------+
| id | data                                               |
+----+----------------------------------------------------+
|  1 | [{"uuid": "a07b50ca-42f0-4d2b-b0a2-f2980deb03d8"}] |
+----+----------------------------------------------------+

This works, but only because I made the value a JSON string (enclosed in double-quotes), and I searched for a fixed position in the array.

I assume you want to search for that value at any position in the array, not only '$[0]'.

The solution in MySQL is to use JSON_TABLE() so you can map the array into rows, and then use a WHERE condition.

mysql> SELECT * FROM tables c CROSS JOIN JSON_TABLE(c.data, '$[*]' COLUMNS (uuid CHAR(36) PATH '$.uuid')) AS j WHERE j.uuid = 'a07b50ca-42f0-4d2b-b0a2-f2980deb03d8';
+----+----------------------------------------------------+--------------------------------------+
| id | data                                               | uuid                                 |
+----+----------------------------------------------------+--------------------------------------+
|  1 | [{"uuid": "a07b50ca-42f0-4d2b-b0a2-f2980deb03d8"}] | a07b50ca-42f0-4d2b-b0a2-f2980deb03d8 |
+----+----------------------------------------------------+--------------------------------------+

If you find the way JSON implements JSON doesn't fit your needs, then I recommend storing data in normal rows and columns. You commented on another answer of mine that this leads to additional joins, but that's a normal part of SQL. It's like saying you don't want to use for-loops in Java.