Using JSON with MySQL 5.7–compatible Amazon Aurora

online_store.sql

 --Creating the database 
DROP DATABASE online_store;
CREATE DATABASE online_store;

--Creating supporting tables
USE online_store;
CREATE TABLE brands (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);
 
CREATE TABLE categories (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);
 
INSERT INTO brands (name) VALUES ('Apple');
INSERT INTO brands (name) VALUES ('Samsung');
INSERT INTO brands (name) VALUES ('Lenovo');
INSERT INTO brands (name) VALUES ('LG');
INSERT INTO brands (name) VALUES ('ASUS');
 
INSERT INTO categories (name) VALUES ('Phones');
INSERT INTO categories (name) VALUES ('Desktop');
INSERT INTO categories (name) VALUES ('Laptop');
INSERT INTO categories (name) VALUES ('Tablets');
 
SELECT * FROM categories;
SELECT * FROM brands;

--Creating JSON table
 
CREATE TABLE products (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
brand_id INT UNSIGNED NOT NULL,
category_id INT UNSIGNED NOT NULL,
attributes JSON NOT NULL);
 
 --Example Inserts
INSERT INTO products
(`name`,`brand_id`,`category_id`,`attributes`)
VALUES
('Samsung 3 Chromebook',2,3,
'{
"dimensions":{"w":11.4,"d":8,"h":0.7},
"weight":2.54,
"color":"black",
"CPU":"Intel Celeron N3060 / 1.6 GHz",
"processor_count":2,
"operating_system":"chrome",
"memory":4,
"Storage":"16 GB SSD"
}'
);
 
select * from products;
 
INSERT INTO products
(`name`,`brand_id`,`category_id`,`attributes`)
VALUES
('Lenovo Notebook',3,3,JSON_OBJECT(
"dimensions",JSON_OBJECT("w",12.6,"d",8.8,"h",0.6),"weight",3.53,"color","platinum silver","CPU","Intel Core i7 (7th Gen) 7500U / 2.7 GHz","processor_count",2,"operating_system","Windows 10","memory",16,"storage","512 GB SSD","interfaces",JSON_ARRAY("USB","Thunderbolt","HDMI","Audio Jack"))
);
 
select * from products where id=2;
 
INSERT INTO products
(`name`,`brand_id`,`category_id`,`attributes`)
VALUES
('Galaxy Tab',2,4,
JSON_MERGE(
'{"display_size":10.1}',
'{"operating_system":"Android Marshmallow"}',
'{"storage":"16 GB"}',
'{"color":"White"}',
'{"memory":2}',
'{"camera":"8 MegaPixel"}'
));
 
select * from products where id=3;
 
INSERT INTO products
(`name`,`brand_id`,`category_id`,`attributes`)
VALUES
('Lenovo Tab',3,4,
JSON_MERGE(
JSON_OBJECT("display_size",10.1),
JSON_OBJECT("operating_system","Android"),
JSON_OBJECT("storage","16 GB"),
JSON_OBJECT("color","Black"),
JSON_OBJECT("memory",16),
JSON_OBJECT("camera","5 MegaPixel")
));
 
select * from products where id=4;
 
 --Adding more rows
INSERT INTO products
(`name`,`brand_id`,`category_id`,`attributes`)
VALUES
('Asus Vivobook',5,3,'{"CPU": "Intel Pentium mobile processor N4200", "color": "black", "memory": 4, "weight": 4.10, "storage": "500 GB SSD", "graphics": "Intel HD Graphics 500", "dimensions": {"d": 11.5, "h": 2.6, "w": 19.8}, "interfaces": ["USB", "Thunderbolt", "HDMI", "Audio Jack"], "processor_count": 4, "operating_system": "Windows 10"}'),
('Macbook Pro',1,3,'{"CPU": "Intel Core i7", "color": "Silver", "memory": 16, "weight": 4.49, "storage": "256 GB SSD", "graphics": "Intel Iris Pro Graphics", "dimensions": {"d": 14.1, "h": 0.7, "w": 9.7}, "interfaces": ["USB", "Thunderbolt", "Audio Jack"], "processor_count": 4, "operating_system": "Mac OS X"}'),
('Apple iPad',1,4,'{"color": "Space Gray", "camera": "1.2 MegaPixel", "memory": 16, "storage": "16 GB", "display_size": 9.7, "operating_system": "iOS 10"}'),
('Apple iPad',1,4,'{"color": "Space Gray", "camera": "1.2 MegaPixel", "memory": 32, "storage": "16 GB", "display_size": 9.7, "operating_system": "iOS 10"}'),
('S8',2,1,'{"color": "Rose Pink", "camera": "8 MegaPixel", "memory": 64, "weight": "152 g", "dimensions": {"d": 14.1, "h": 0.7, "w": 9.7}, "Screen Size": 5.8, "operating_system": "Android Nougat"}'),
('Note',2,1,'{"color": "Black ", "camera": "8 MegaPixel", "memory": 64, "weight": "152 g", "dimensions": {"d": 0.3, "h": 3, "w": 6.3}, "Screen Size": 5.8, "operating_system": "Android Nougat"}'),
('iPhone 7 plus',1,1,'{"color": "Silver ", "camera": "12 MegaPixel", "memory": 32, "weight": "120 g", "dimensions": {"d": 3.1, "h": 0.29, "w": 6.2}, "Screen Size": 5.5, "operating_system": "iOS 10"}'),
(' iPhone 6',1,1,'{"color": "Silver ", "camera": "12 MegaPixel", "memory": 16, "weight": "150 g", "dimensions": {"d": 3.1, "h": 0.29, "w": 6.2}, "Screen Size": 4.7, "operating_system": "iOS 10"}');
 
 --Select Queries
SELECT * FROM products WHERE attributes like '{"color": "Black", "camera": "5 MegaPixel", "memory": 16, "storage": "16 GB", "display_size": 10.1, "operating_system": "Android"}';
 
SELECT name,attributes->'$.operating_system' as operating_system FROM products WHERE JSON_EXTRACT(attributes,'$.operating_system') like '"Android%';
 
SELECT name,JSON_UNQUOTE(attributes->'$.operating_system') as operating_system FROM products WHERE JSON_UNQUOTE(JSON_EXTRACT(attributes,'$.operating_system')) like 'Android%';
 
 
 --Adding indexes
 --This gives an error
alter table products add key idx_attributes (attributes);
 
alter table products add column memory int as (attributes->'$.memory');
alter table products add key idx_memory (memory);
 
EXPLAIN SELECT name,memory FROM products where memory=16;
 
select id,name,JSON_KEYS(attributes) from products where name like 'Apple iPad';
 
select * from products where id=9;

--Update Queries
UPDATE products SET attributes=JSON_INSERT(attributes,"$.network","CDMA") where id=9;
 
select * from products where id=9;
 
UPDATE products SET attributes=JSON_REPLACE(attributes,"$.operating_system","Android Oreo") where id=9;
 
select * from products where id=9;
 
UPDATE products SET attributes=JSON_SET(attributes,"$.color","Gray") WHERE id=9;
 
SELECT * FROM products WHERE id=9;
 
UPDATE products SET attributes=JSON_ARRAY_APPEND(attributes,"$.network","GSM") WHERE id=9;
 
SELECT * FROM products WHERE id=9;
 
--Delete Queries 
UPDATE products SET attributes=JSON_REMOVE(attributes,"$.network") WHERE id=9;
 
select * from products where id=9;
 
SELECT name,attributes->'$."Screen Size"' as screen_size FROM products WHERE JSON_EXTRACT(attributes,'$."Screen Size"')<5;
 
DELETE FROM products WHERE JSON_EXTRACT(attributes,'$."Screen Size"')<5;
 
SELECT name,attributes->'$."Screen Size"' as screen_size FROM products WHERE JSON_EXTRACT(attributes,'$."Screen Size"')<5;