MySQL usage of new JSON field

As a part from my new project, I'm using MySQL JSON field which was introduced in version 5.7, functions are described in https://dev.mysql.com/doc/refman/5.7/en/json-functions.html Creating JSON data is pretty simple like:

CREATE TABLE `events` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `site_id` INT(11) NOT NULL,
    `category_id` INT(11) NOT NULL,
    `name` VARCHAR(250) NOT NULL,
    `value` TEXT NULL,
    `added_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `custom_data` JSON NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `site_id` (`site_id`, `category_id`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=0;

As you can see Create Table syntax is same, difference is in

`custom_data` JSON

. So let's insert some data to it.

INSERT INTO `events` (`id`, `site_id`, `category_id`, `name`, `value`, `added_time`, `custom_data`) 
VALUES (3, 1, 4, 'fsafsa', '21', '2015-12-21 23:05:56', NULL); 

INSERT INTO `events` (`id`, `site_id`, `category_id`, `name`, `value`, `added_time`, `custom_data`) 
VALUES (4, 1, 4, 'fsafsa', '21', '2015-12-21 23:06:09', NULL); 

INSERT INTO `events` (`id`, `site_id`, `category_id`, `name`, `value`, `added_time`, `custom_data`) 
VALUES (5, 1, 4, 'fsafsa', '2кири', '2015-12-21 23:06:16', NULL); 

INSERT INTO `events` (`id`, `site_id`, `category_id`, `name`, `value`, `added_time`, `custom_data`) 
VALUES (6, 1, 4, 'fsafsa', '2кири', '2015-12-25 10:11:54', '{"dada": {"rpb": "22"}, "test": "dada"}'); 

INSERT INTO `events` (`id`, `site_id`, `category_id`, `name`, `value`, `added_time`, `custom_data`) 
VALUES (7, 1, 1, 'test', '1', '2015-12-25 17:26:04', NULL); 

INSERT INTO `events` (`id`, `site_id`, `category_id`, `name`, `value`, `added_time`, `custom_data`) 
VALUES (8, 1, 1, '1da', '2', '2015-12-25 17:26:10', '{"test": "dada"}');

And lets assume that we want to filter data in JSON field.

SELECT  
    id, 
    custom_data 
FROM 
    `events` 
WHERE 
    JSON_UNQUOTE(custom_data->"$.test") = "dada"

Result is:

+----+-----------------------------------------+ 
| id | custom_data                             | 
+----+-----------------------------------------+ 
|  6 | {"dada": {"rpb": "22"}, "test": "dada"} | 
|  8 | {"test": "dada"}                        | 
+----+-----------------------------------------+ 
2 rows in set (0.00 sec)

Or if we want to check existence of sub element:

SELECT id, custom_data  
FROM `events` 
WHERE JSON_CONTAINS_PATH(custom_data, "all", "$.dada.rpb");

Result is:

+----+-----------------------------------------+ 
| id | custom_data                             | 
+----+-----------------------------------------+ 
|  6 | {"dada": {"rpb": "22"}, "test": "dada"} | 
+----+-----------------------------------------+ 
1 row in set (0.00 sec)

And one more example for extracting value from JSON:

SELECT
   id,
   JSON_UNQUOTE(JSON_EXTRACT(custom_data, '$.test')) AS value
FROM `events`
WHERE JSON_EXTRACT(custom_data, '$.test') IS NOT NULL;

SELECT 
    id, value
FROM 
    table_name
WHERE 
    condition;

It's look pretty easy, right ? :)

← Back to all posts