how to perform a SELECT on a JSON column in mysql/mariaDB

Question

how to apply WHERE clause on JSON column to perform a SELECT query on a table which is having two columns (id Integer, attr JSON). The JSON is nested and in the filter condition there is only one key value pair of json is allowed. This key value pair can be anywhere in the Josn.

+----+-----------------------------------------------------------------
| id | attr                                                                                          
|
+----+-----------------------------------------------------------------
|  1 | {"id":"0001","type":"donut","name":"Cake","ppu":0.55}                                         
|
|  2 | {"id":"0002","type":"donut","name":"Cake","ppu":0.55,"batters":
       {"batter1":100,"batter2":200}} 
+----+-----------------------------------------------------------------

Answer 1

In MariaDB 10.2, you can use the JSON functions.

For example, if you want to SELECT all donuts from your database, you do:

SELECT * FROM t WHERE JSON_CONTAINS(attr, '"donut"', '$.type');

Note: In MariaDB, JSON functions work with all text data types (VARCHAR, TEXT etc.). The JSON type is simply an alias for LONGTEXT.