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.
>Solution :
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.