Mysql 查找JSON以及JSON 数组

I think you can achieve this only in Mysql 5.7.

In version 5.7 you can do something like:

SELECT JSON_EXTRACT(json_field, '$.name');

and it will extract only the name key from json object.

Search all items with the ‘JavaScript’ tag:

SELECT * FROM `table` WHERE JSON_CONTAINS(json_field, '["JavaScript"]');

Find all items with tags starting ‘Java’:

SELECT * FROM `table` WHERE JSON_SEARCH(json_field, 'one', 'Java%') IS NOT NULL;

use ‘one’ to find the first match or ‘all’ to find all matches

You can extract the Twitter nickname using a JSON path:

SELECT name, json_field->"$.twitter" AS `twitter` FROM `user`;

You can also reference a JSON path in the WHERE clause to only return users with a Twitter account:

SELECT name, profile->"$.twitter" AS `twitter` FROM `user` WHERE profile->"$.twitter" IS NOT NULL;

You can do more things like:

• Creating JSON Values
• Normalization, Merging, and Autowrapping of JSON Values
• Searching and Modifying JSON Values
• Comparison and Ordering of JSON Values
• Aggregation of JSON Values

for more info please refer to: https://dev.mysql.com/doc/refman/5.7/en/json.html

SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "2") is not null 

SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "6") is not null

You may search an array of integers as follows:

  JSON_CONTAINS('[1,2,3,4,5]','7','$') Returns: 0
  JSON_CONTAINS('[1,2,3,4,5]','1','$') Returns: 1

You may search an array of strings as follows:

  JSON_CONTAINS('["a","2","c","4","x"]','"x"','$') Returns: 1
  JSON_CONTAINS('["1","2","3","4","5"]','"7"','$') Returns: 0

Note: JSON_CONTAINS returns either 1 or 0

In your case you may search using a query like so:

SELECT * from my_table
WHERE JSON_CONTAINS(data, '2', '$');