[MDEV-11897] Where more/less/more or equal/less or equal conditions for JSON nested arrays dosn't work Created: 2017-01-24  Updated: 2017-01-28  Resolved: 2017-01-25

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.2.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Andrii Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: JSON
Environment:

Ubuntu 14.04 LTS


 Description    

CREATE TABLE `test` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `data` json NOT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
 
 
INSERT INTO `test` (`data`) VALUES 
    ('
 
        {
 
            "node": 1,
 
            "orders": [
 
                {
 
                    "ts": "Jan 13, 2016 04:38pm",
 
                    "order_id": 1,
 
                    "products": [
 
                        {
 
                            "Qty": 1,
 
                            "SKU": 1,
 
                            "price": 40,
 
                            "total": 40
 
                        },
 
                        {
 
                            "Qty": 2,
 
                            "SKU": 2,
 
                            "price": 80,
 
                            "total": 160
 
                        },
 
                        {
 
                            "Qty": 5,
 
                            "SKU": 3,
 
                            "price": 10,
 
                            "total": 50
 
                        }
 
                    ]
 
                }
 
            ]
 
        }
 
    '),
 
    ('
 
        {
 
            "node": 2,
 
            "orders": [
 
                {
 
                    "ts": "Jan 13, 2016 04:38pm",
 
                    "order_id": 2,
 
                    "products": [
 
                        {
 
                            "Qty": 3,
 
                            "SKU": 1,
 
                            "price": 40,
 
                            "total": 120
 
                        },
 
                        {
 
                            "Qty": 1,
 
                            "SKU": 3,
 
                            "price": 10,
 
                            "total": 10
 
                        }
 
                    ]
 
                }
 
            ]
 
        }
 
    ');

Conditions working fine for the first level keys:

SELECT * FROM `test` 
WHERE `data`->'$.node' > 1;

Conditions works wrong:

SELECT * FROM `test` 
WHERE `data`->'$.orders[*].order_id' > 1;
 
 
 
SELECT * FROM `test` 
WHERE `data`->'$.orders[*].products[*].price' > 50;
 
 
 
SELECT * FROM `test` 
WHERE `data`->'$.orders[*].products[*].total' < 30;



 Comments   
Comment by Andrii Nikitin (Inactive) [ 2017-01-25 ]

json column type is not supported in MariaDB yet, so you should receive syntax error while trying to create such table in MariaDB at the moment.
Closing this as duplicate of https://jira.mariadb.org/browse/MDEV-9144

Please note that these examples cannot work properly as you can't compare arrays to constants.
You should receive an error or at least a warning, but MySQL just fools you claiming that it is doing job properly, like in example below:

mysql> set @order=json_array(2);
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> select @order;
 
+--------+
 
| @order |
 
+--------+
 
| [2]    |
 
+--------+
 
1 row in set (0.00 sec)
 
 
 
mysql> select @order > 1;
 
+------------+
 
| @order > 1 |
 
+------------+
 
|          0 |
 
+------------+
 
1 row in set (0.00 sec)

My personal belief is that you should avoid processing JSON inside RDBMS - in long run it will bring you more problems than benefits.

But if you still sure that you want to do it - the best idea I can think about such task in MariaDB of is to replace 'json' in column definition to 'text' and generate queries like this (if you know maximum number of products per order):

SELECT * FROM `test` where 
ifnull(json_extract(json_extract(data, '$.orders[*].products[*].price'),"$[0]"),0) > 50
 
or
 
ifnull(json_extract(json_extract(data, '$.orders[*].products[*].price'),"$[1]"),0) > 50
 
or
 
ifnull(json_extract(json_extract(data, '$.orders[*].products[*].price'),"$[2]"),0) > 50
 
or
 
ifnull(json_extract(json_extract(data, '$.orders[*].products[*].price'),"$[3]"),0) > 50
 
or
 
ifnull(json_extract(json_extract(data, '$.orders[*].products[*].price'),"$[4]"),0) > 50;

use OR if you need to find products where at least one price is bigger than 50 and AND if you want to make sure that every price is bigger (i.e. compare array to constant).

Comment by Andrii [ 2017-01-28 ]

Thank you @Andrii Nikitin for such fast and detailed answer! You are right that RDBMS always was not the best place for JSON, but it looks like Not Only SQL strategy of RDBMS development is on the right course with JSON support and the most way is already passed.

Generated at Thu Feb 08 07:53:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.