Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.2.3
-
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; |