mysql query exact match on json field not working

Question

table looks like this:

my_table
id (int)   create_time (timestamp)   meta_data (json)
1          2019-01-20 18:35:42        {"my property": "123"}
2          2019-01-20 19:35:42        {"more data": "456"}

I've tried querying with:

SELECT * FROM my_table
WHERE meta_data = '{"my property": "123"}';

SELECT * FROM my_table
WHERE meta_data = '\{\"my property\"\: \"123\"\}';

And it doesn't work, how can I query an exact match on a json field string?

I noticed this DOES work...

SELECT * FROM my_table
WHERE meta_data LIKE '\{\"my property\"\: \"123\"\}';

Do I need to use LIKE? Why = not work?

Answer 1

I know the JSON field is a special field type that is designed to let you easily query specific properties, but I wanted to be able to just check against the full JSON easily. And clearly the JSON field has some parameters that cause the = not to work as I expected.

This is the solution I figured out, cast JSON as CHAR:

SELECT * FROM my_table
WHERE CAST(meta_data as CHAR) = '{"my property": "123"}';

Another option:

SELECT * FROM my_table
WHERE meta_data = CAST('{"my property": "123"}' AS JSON);

You can also obtain JSON values by casting values of other types to the JSON type using CAST(value AS JSON);" https://dev.mysql.com/doc/refman/5.7/en/json-creation-functions.html#function_json-quote

Answer 2

Use JSON_CONTAINS:

SELECT *
FROM my_table
WHERE JSON_CONTAINS(meta_data, '"123"', '.$"my property"');

Demo

Notes:

• Since the target value to match for the key my property is a literal string, we need to also search for the double quotes.
• To escape the key my property in the JSON path, we can escape that using double quotes.