How to search JSON array in MySQL?

by

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', '$');

More Related Contents:

  1. How to search JSON data in MySQL?
  2. MySQL JSON: How to find object from key value
  3. Using MySQL JSON field to join on a table
  4. How to update JSON data type column in MySQL 5.7.10?
  5. MySQL search json value by key in array
  6. How to create a MySQL hierarchical recursive query?
  7. MySQL Error 1215: Cannot add foreign key constraint
  8. MySQL Server has gone away when importing large sql file
  9. mySQL convert varchar to date
  10. Slow query when using ORDER BY
  11. How to import XML file into MySQL database table using XML_LOAD(); function
  12. MySQL stored procedure vs function, which would I use when?
  13. Problems with contenttypes when loading a fixture in Django
  14. How to convert time to the time zone of the iPhone device?
  15. MySQL: Sort GROUP_CONCAT values
  16. Entity Framework wizard crashes on MySQL
  17. How can I set a maximum number of rows in MySQL table?
  18. Dynamic table names in stored procedure function
  19. How to alter a column and change the default value?
  20. Alter MySQL table to add comments on columns
  21. MySQL – how many rows can I insert in one single INSERT statement?
  22. How do I show the schema of a table in a MySQL database?
  23. MySQL Stored Procedure Error Handling
  24. Mysql delete statement with limit
  25. How big can a MySQL database get before performance starts to degrade
  26. MySQL: How do I use delimiters in triggers?
  27. MySQL connection over SSH tunnel
  28. QMYSQL driver available but not loaded
  29. UTF8 problem with MySQL 5
  30. How can I make a table in MySQL called “order”?

Leave a Comment