MySQL JSON: How to find object from key value

by

[*]

MySQL 8.0 provides JSON_TABLE() to help with cases like this.

select field_options.* from fields cross join 
json_table(fields.options, 
'$[*]' columns(
  text text path '$.text',
  value text path '$.value'
 )
) as field_options 
where field_options.value = 1;
+-------+-------+
| text  | value |
+-------+-------+
| Grass | 1     |
+-------+-------+

But you have to do this complex JSON_TABLE() expression every time you want to write such a query.

It would be simpler to not use JSON — instead, store data in a table with normal columns (one row per text/value pair). Then you can search for values you want in either column.

SELECT * FROM field_options WHERE value="1";

99% of the uses of JSON in MySQL that I see on Stack Overflow would be solved easily by not using JSON.

More Related Contents:

  1. How to search JSON data in MySQL?
  2. Using MySQL JSON field to join on a table
  3. How to update JSON data type column in MySQL 5.7.10?
  4. MySQL search json value by key in array
  5. How to convert result table to JSON array in MySQL
  6. Storing Data in MySQL as JSON
  7. How do I generate nested json objects using mysql native json functions?
  8. How can write queries in MySQL that can parse JSON data in a column?
  9. Convert JSON array in MySQL to rows
  10. Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?
  11. How to retrieve JSON data from MySQL?
  12. How to create and insert a JSON object using MySQL queries?
  13. MySQL vs. JSON – Why? [closed]
  14. How to search JSON array in MySQL?
  15. how to get result form mysql query in specific ids [closed]
  16. Why can’t this entry be added because of Foreign Key error?
  17. MySQL AUTO_INCREMENT does not ROLLBACK
  18. Create new user in MySQL and give it full access to one database
  19. Most efficient way to get table row count
  20. Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?
  21. Allow all remote connections, MySQL
  22. Import CSV to MySQL
  23. MySQL Install: ERROR: Failed to build gem native extension
  24. MySQL said: Documentation #1045 – Access denied for user ‘root’@’localhost’ (using password: NO)
  25. How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?
  26. MySQL connection over SSH tunnel – how to specify other MySQL server?
  27. Storing IPv6 Addresses in MySQL
  28. Defining Composite Key with Auto Increment in MySQL
  29. How to use Table output from stored MYSQL Procedure
  30. Get total hours worked in a day mysql

Leave a Comment