CREATE TABLE `product` (
`SKU` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`type` varchar(200) NOT NULL,
`attr` json DEFAULT NULL,
PRIMARY KEY (`SKU`)
) ENGINE=InnoDB;
INSERT INTO `product` (`type`, `attr`) VALUES ( 'book', ' {"title":"Understanding the Linux Kernel, Third Edition", "ISBN":"978-0596005658", "authors" : ["Daniel P. Bovet", "Marco Cesati"] }');
INSERT INTO `product` (`type`, `attr`) VALUES ( 'book', ' [{ "k": "title", "v": "Head First Java, 2nd Edition" }, { "k": "ISBN", "v": "978-0596009205" }, { "k": "author", "v": "Kathy Sierra" }, { "k": "author", "v": "Bert Bates" }, { "k": "genre", "v": ["computing", "software"] } ] ');
INSERT INTO `product` (`type`, `attr`) VALUES ( 'book', ' {"title":"Understanding the Linux Kernel, Third Edition", "ISBN":"978-0596005658", "authors" : ["Daniel P. Bovet", "Marco Cesati"], "detail": {"web_site":"amazon.com", "price":"40", "currency":"TL"} }');
SELECT * FROM `product` WHERE JSON_EXTRACT(attr, "$.ISBN") = '978-0596005658';
SELECT * FROM `product` WHERE JSON_EXTRACT(attr, "$.title") = 'Understanding the Linux Kernel, Third Edition';
SELECT * FROM `product` WHERE JSON_EXTRACT(attr, "$.detail.web_site") = 'amazon.com';
SELECT * FROM `product` WHERE JSON_CONTAINS_PATH(attr, 'one', "$.detail.web_site");
EXPLAIN SELECT * FROM product;
EXPLAIN SELECT * FROM `product` WHERE JSON_EXTRACT(attr, "$.ISBN") = '978-0596005658';
ALTER TABLE `product` ADD `isbn` VARCHAR(15) GENERATED ALWAYS AS (JSON_UNQUOTE( JSON_EXTRACT(attr, '$.ISBN') )) VIRTUAL;
CREATE INDEX index_isbn ON product (`isbn`);
`SKU` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`type` varchar(200) NOT NULL,
`attr` json DEFAULT NULL,
PRIMARY KEY (`SKU`)
) ENGINE=InnoDB;
INSERT INTO `product` (`type`, `attr`) VALUES ( 'book', ' {"title":"Understanding the Linux Kernel, Third Edition", "ISBN":"978-0596005658", "authors" : ["Daniel P. Bovet", "Marco Cesati"] }');
INSERT INTO `product` (`type`, `attr`) VALUES ( 'book', ' [{ "k": "title", "v": "Head First Java, 2nd Edition" }, { "k": "ISBN", "v": "978-0596009205" }, { "k": "author", "v": "Kathy Sierra" }, { "k": "author", "v": "Bert Bates" }, { "k": "genre", "v": ["computing", "software"] } ] ');
INSERT INTO `product` (`type`, `attr`) VALUES ( 'book', ' {"title":"Understanding the Linux Kernel, Third Edition", "ISBN":"978-0596005658", "authors" : ["Daniel P. Bovet", "Marco Cesati"], "detail": {"web_site":"amazon.com", "price":"40", "currency":"TL"} }');
SELECT * FROM `product` WHERE JSON_EXTRACT(attr, "$.ISBN") = '978-0596005658';
SELECT * FROM `product` WHERE JSON_EXTRACT(attr, "$.title") = 'Understanding the Linux Kernel, Third Edition';
SELECT * FROM `product` WHERE JSON_EXTRACT(attr, "$.detail.web_site") = 'amazon.com';
SELECT * FROM `product` WHERE JSON_CONTAINS_PATH(attr, 'one', "$.detail.web_site");
EXPLAIN SELECT * FROM product;
EXPLAIN SELECT * FROM `product` WHERE JSON_EXTRACT(attr, "$.ISBN") = '978-0596005658';
ALTER TABLE `product` ADD `isbn` VARCHAR(15) GENERATED ALWAYS AS (JSON_UNQUOTE( JSON_EXTRACT(attr, '$.ISBN') )) VIRTUAL;
CREATE INDEX index_isbn ON product (`isbn`);
Comments
Post a Comment