Skip to content

Instantly share code, notes, and snippets.

@VeryFatBoy
Created November 13, 2022 00:30
Embed
What would you like to do?
DROP DATABASE IF EXISTS e_store;
CREATE DATABASE IF NOT EXISTS e_store
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
USE e_store;
CREATE TABLE brands (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(250) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE categories (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(250) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE products (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(250) NOT NULL,
brand_id INT UNSIGNED NOT NULL,
category_id INT UNSIGNED NOT NULL,
attributes JSON NOT NULL, -- NOT NULL ensures correct JSON
PRIMARY KEY(id),
INDEX CATEGORY_ID(category_id ASC),
INDEX BRAND_ID(brand_id ASC)
);
-- 1:m between brands and products.
-- 1:m between categories and products.
------------
-- 1. CREATE
------------
INSERT INTO brands (name) VALUES
('Samsung'),
('Nokia'),
('Canon');
INSERT INTO categories (name) VALUES
('Television'),
('Mobile Phone'),
('Camera');
-- Televisions
/* Nested JSON data
{
"screen" : "50 inch",
"resolution" : "2048 x 1152 pixels",
"ports" : {
"hdmi" : 1,
"usb" : 3
},
"speakers" : {
"left" : "10 watt",
"right" : "10 watt"
}
}
*/
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Prime', '1', '1',
'{ "screen" : "50 inch",
"resolution" : "2048 x 1152 pixels",
"ports" : {"hdmi" : 1, "usb" : 3},
"speakers" : {"left" : "10 watt", "right" : "10 watt"}
}'),
('Octoview', '1', '1',
'{ "screen" : "40 inch",
"resolution" : "1920 x 1080 pixels",
"ports" : {"hdmi" : 1, "usb" : 2},
"speakers" : {"left" : "10 watt", "right" : "10 watt"}
}'),
('Dreamer', '1', '1',
'{ "screen" : "30 inch",
"resolution" : "1600 x 900 pixels",
"ports" : {"hdmi" : 1, "usb" : 1},
"speakers" : {"left" : "10 watt", "right" : "10 watt"}
}'),
('Bravia', '1', '1',
'{ "screen" : "25 inch",
"resolution" : "1366 x 768 pixels",
"ports" : {"hdmi" : 1, "usb" : 0},
"speakers" : {"left" : "5 watt", "right" : "5 watt"}
}'),
('Proton', '1', '1',
'{ "screen" : "20 inch",
"resolution" : "1280 x 720 pixels",
"ports" : {"hdmi" : 0, "usb" : 0},
"speakers" : {"left" : "5 watt", "right" : "5 watt"}
}');
-- Mobile Phones
/* JSON with array
{
"body" : "5.11 x 2.59 x 0.46 inches",
"display" : "4.5 inches",
"network" : [
"GSM",
"CDMA",
"HSPA",
"EVDO"
],
"os" : "Android Jellybean v4.3",
"resolution" : "720 x 1280 pixels",
"sim" : "Micro-SIM",
"weight" : "143 grams"
}
*/
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Desire', '2', '2', JSON_BUILD_OBJECT("network",
JSON_ARRAY_PUSH_STRING('["GSM", "CDMA", "HSPA"]', 'EVDO'),
"body",
"5.11 x 2.59 x 0.46 inches",
"weight",
"143 grams",
"sim",
"Micro-SIM",
"display",
"4.5 inches",
"resolution",
"720 x 1280 pixels",
"os",
"Android Jellybean v4.3"
)
),
('Passion', '2', '2', JSON_BUILD_OBJECT("network",
JSON_ARRAY_PUSH_STRING('["GSM", "CDMA"]', 'HSPA'),
"body",
"6.11 x 3.59 x 0.46 inches",
"weight",
"145 grams",
"sim",
"Micro-SIM",
"display",
"4.5 inches",
"resolution",
"720 x 1280 pixels",
"os",
"Android Jellybean v4.3"
)
),
('Emotion', '2', '2', JSON_BUILD_OBJECT("network" ,
JSON_ARRAY_PUSH_STRING('["GSM", "CDMA"]', 'EVDO'),
"body",
"5.50 x 2.50 x 0.50 inches",
"weight",
"125 grams",
"sim",
"Micro-SIM",
"display",
"5.00 inches",
"resolution",
"720 x 1280 pixels",
"os",
"Android KitKat v4.3"
)
),
('Sensation', '2', '2', JSON_BUILD_OBJECT("network",
JSON_ARRAY_PUSH_STRING('["GSM", "HSPA"]', 'EVDO'),
"body",
"4.00 x 2.00 x 0.75 inches",
"weight",
"150 grams",
"sim",
"Micro-SIM",
"display",
"3.5 inches",
"resolution",
"720 x 1280 pixels",
"os",
"Android Lollipop v4.3"
)
),
('Joy', '2', '2', JSON_BUILD_OBJECT("network",
JSON_ARRAY_PUSH_STRING('["CDMA", "HSPA"]', 'EVDO'),
"body",
"7.00 x 3.50 x 0.25 inches",
"weight",
"250 grams",
"sim",
"Micro-SIM",
"display",
"6.5 inches",
"resolution",
"1920 x 1080 pixels",
"os",
"Android Marshmallow v4.3"
)
);
-- Cameras
/* Flat JSON
{
"sensor_type" : "CMOS",
"processor" : "Digic DV III",
"scanning_system" : "progressive",
"mount_type" : "PL",
"monitor_type" : "LCD"
}
*/
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Explorer', '3', '3',
'{ "sensor_type" : "CMOS",
"processor" : "Digic DV III",
"scanning_system" : "progressive",
"mount_type" : "PL",
"monitor_type" : "LCD"
}'),
('Runner', '3', '3',
'{ "sensor_type" : "CMOS",
"processor" : "Digic DV II",
"scanning_system" : "progressive",
"mount_type" : "PL",
"monitor_type" : "LED"
}'),
('Traveler', '3', '3',
'{ "sensor_type" : "CMOS",
"processor" : "Digic DV II",
"scanning_system" : "progressive",
"mount_type" : "PL",
"monitor_type" : "LCD"
}'),
('Walker', '3', '3',
'{ "sensor_type" : "CMOS",
"processor" : "Digic DV I",
"scanning_system" : "progressive",
"mount_type" : "PL",
"monitor_type" : "LED"
}'),
('Jumper', '3', '3',
'{ "sensor_type" : "CMOS",
"processor" : "Digic DV I",
"scanning_system" : "progressive",
"mount_type" : "PL",
"monitor_type" : "LCD"
}');
----------
-- 2. READ
----------
SELECT JSON_GET_TYPE(attributes)
FROM products;
-- All the rows are JSON objects.
-- Now let’s find any Televisions that have
-- one or more USB port(s) and one or more HDMI port(s).
SELECT * FROM products
WHERE category_id = 1
AND attributes::ports::usb > 0
AND attributes::ports::hdmi > 0;
-- Notice that we can use the double-colon (::) to specify a
-- path to the specific attribute we are interested in.
------------
-- 3. UPDATE
------------
-- First, we’ll create a new attribute called body_color for Televisions.
UPDATE products
SET attributes::$body_color = 'red'
WHERE category_id = 1;
-- If we check the attributes, we should see that body_color has been added.
SELECT attributes
FROM products
WHERE category_id = 1;
-- Next, let’s add a chipset for Mobile Phones.
UPDATE products
SET attributes::$chipset = 'Qualcomm'
WHERE category_id = 2;
-- If we check the attributes, we should see that chipset has been added
SELECT attributes
FROM products
WHERE category_id = 2;
-- We can also update the existing value of the chipset to a new value.
UPDATE products
SET attributes::$chipset = 'Qualcomm Snapdragon'
WHERE category_id = 2;
SELECT attributes
FROM products
WHERE category_id = 2;
------------
-- 4. DELETE
------------
SELECT attributes
FROM products
WHERE category_id = 3;
UPDATE products
SET attributes = JSON_DELETE_KEY(attributes, 'mount_type')
WHERE category_id = 3;
SELECT attributes
FROM products
WHERE category_id = 3;
--------------------------
-- More complex operations
--------------------------
SELECT *
FROM products
WHERE category_id = 2
AND attributes::$os LIKE '%Jellybean%';
DELETE FROM products
WHERE category_id = 2
AND attributes::$os LIKE '%Jellybean%';
SELECT *
FROM products
WHERE category_id = 2;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment