How to search records using JSON function in mariadb from json array

I am learning JSON function in mariaDB where I have

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}');

I want to find how many records are there where Cuts.name=”Theatrical”

SELECT * FROM `products` WHERE JSON_VALUE(attr,'$.cuts.name')='Theatrical'

I am getting 0 results here. How to search data from json array ?

Answer

The square brackets [] should be used for arrays.

You can use JSON_EXTRACT(attr, "$.cuts[*].name") nested within JSON_CONTAINS() function with '"Theatrical"' argument to determine whether the tuples for name elements of cuts array contain '"Theatrical"' :

SELECT  COUNT(*)
  FROM `products` 
 WHERE JSON_CONTAINS( JSON_EXTRACT(attr, "$.cuts[*].name"), '"Theatrical"' )

Demo

Advertisement

Answer