By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS products (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
type VARCHAR(1) NOT NULL,
name VARCHAR(40) NOT NULL,
format VARCHAR(20) NOT NULL,
price FLOAT(5, 2) NOT NULL,
attr JSON NOT NULL
);
INSERT INTO products (type, name, format, price, attr)
VALUES ('M', 'Aliens', 'Blu-ray', 13.99,
'{"video": {"resolution": "1080p", "aspectRatio": "1.85:1"},
"cuts": [{"name": "Theatrical", "runtime": 138},
{"name":"Special Edition", "runtime": 155}],
"audio": ["DTS HD", "Dolby Surround"]}');
INSERT INTO products (type, name, format, price, attr)
VALUES ('B', 'Foundation', 'Paperback', 7.99,
'{"author": "Isaac Asimov", "page_count": 296}');
INSERT INTO products (type, name, format, price, attr)
VALUES ('M', 'Aliens', 'Blu-ray', 13.99,
'{"video": {"resolution": "1080p", "aspectRatio": "1.85:1"},
"cuts": [{"name":"Theatrical", "runtime": 156}],
"audio": ["DTS HD", "Dolby Surround"]}');
SELECT COUNT(*)
FROM `products`
WHERE JSON_CONTAINS( JSON_EXTRACT(attr, "$.cuts[*].name"), '"Theatrical"' );
| COUNT(*) |
|---|
| 2 |