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

Question

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 ?

Barbaros Özhan · Accepted Answer · 2020-04-29 13:18:39Z

1

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

edited Apr 29, 2020 at 13:18

answered Apr 29, 2020 at 13:13

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related