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}} 
+----+-----------------------------------------------------------------

@TimBiegeleisen I should be able to filter Json Column. FYU (select id from table_name where attr contains "batter2":200") — wenky, Feb 15 '18 at 10:36

score 30 · Accepted Answer · answered Feb 15 '18 at 12:12

30

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.

answered Feb 15 '18 at 12:12

markusjm

2,358
1
11
23

MySQL 5.7 has some JSON functions. – Rick James Feb 26 '18 at 02:32

MarAvFe · Answer 2 · 2020-01-07T15:38:47.573

16

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

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

edited Jan 07 '20 at 15:38

answered Oct 26 '18 at 21:56

MarAvFe

468
4
15

score 2 · Answer 3 · answered May 20 '20 at 18:34

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';

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

3 Answers3