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

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.

Similarly to markusjm's answer, you can select directly from the json by field like:

SELECT json_extract(attr, '$.type') FROM t;

If you are still using MySQL 5.6 (has no JSON parsing support) we can use the substring_index functions to parse json data.

Here is a working example:

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `attr` longtext COLLATE utf8_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO products (attr, created_at) 
VALUES 
('{"id":"0001","type":"donut","name":"Cake","ppu":0.55}', now()),
('{"id":"0002","type":"donut","name":"Cake","ppu":0.55,"batters":{"batter1":100,"batter2":200}}', now()),
('{"id":"0003","type":"apple","name":"Apple","ppu":0.60}', now()),
('{"id":"0003","type":"orange","name":"Orange","ppu":0.65}', now());

select 
    substring_index(substring_index(attr, '"type":"', -1), '",', 1) AS product_type
from products
having product_type = 'donut';