Hi everyone,
I’m currently working with Manticore and facing a challenge with filtering values within a subquery. Here’s the scenario:
I have a table t with the following SELECT statement:
SELECT features FROM t LIMIT 10;
The result looks like this:
[
{"type": "w", "name": "height", "values": ["2 cm", "..."]},
{"type": "2", "name": "length", "values": ["8 cm"]},
...
]
Now, I want to filter based on the values. An example attempt that didn’t work is:
SELECT id, ANY(x='w' FOR x IN features.type) AS cond
FROM t
WHERE cond=1;
The result is empty.
Does anyone have an idea on how to correctly filter by values? Any help would be greatly appreciated!
Thanks in advance!
Sergey
2
mysql> drop table if exists t; create table t(features json); insert into t(features) values('[{"type": "w", "name": "height", "values": ["2 cm", "..."]}, {"type": "2", "name": "length", "values": ["8 cm"]}]'); select id, ANY(x.type='w' FOR x IN features) AS cond from t where cond=1;
--------------
drop table if exists t
--------------
Query OK, 0 rows affected (0.04 sec)
--------------
create table t(features json)
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
insert into t(features) values('[{"type": "w", "name": "height", "values": ["2 cm", "..."]}, {"type": "2", "name": "length", "values": ["8 cm"]}]')
--------------
Query OK, 1 row affected (0.01 sec)
--------------
select id, ANY(x.type='w' FOR x IN features) AS cond from t where cond=1
--------------
+---------------------+------+
| id | cond |
+---------------------+------+
| 1515939651171385543 | 1 |
+---------------------+------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---
thanks for the quick reply 
it works.
is it also possible to filter by values? for example “2 cm”
Sergey
4
I believe not. There’s no syntax for searching inside an array inside another array.
good and thanks for the quick reply. i will probably use a different species then.
Hello @Sergey ,
I have another problem.
I have a table where the valid_to column is greater than NOW(). Still I am not getting any data back.
However, when I run the following query, I get data:
SELECT id, prices, ANY((x.valid_to > now()) FOR x IN prices) AS cond FROM de_de_products where cond = 1;
However, if I set cond = 0 I get data.
mysql> SELECT id, prices, ANY((x.valid_to > now()) FOR x IN prices) AS cond FROM de_de_products where cond = 0;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+------+
| id | prices | cond |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+------+
| 3121397628 | [{"price":1,"currency":"EUR","valid_from":-62135596800,"valid_to":4876886074,"price_type":null,"tax":19,"upper_bound":null,"lower_bound":null}] | 0 |
| 1831296373 | [{"price":101,"currency":"EUR","valid_from":-62135596800,"valid_to":4876886026,"price_type":null,"tax":19,"upper_bound":null,"lower_bound":null}] | 0 |
| 2649278998 | [{"price":2280,"currency":"EUR","valid_from":-62135596800,"valid_to":4876885184,"price_type":null,"tax":19,"upper_bound":null,"lower_bound":null}] | 0 |
Do you have any idea what the problem is?
Thx!
Sergey
7
However, when I run the following query, I get data:
What data do you get with cond = 1?
However, if I set cond = 0 I get data.
The result looks correct to me. You want where cond = 0. It returns you the records with cond = 0.
I would like to have all hits that are cond = 1.
but there is no match even if the price range is within the time period i am looking for
ps: valid_from and valid_to are Unix timestamps
Sergey
9
1 Like
tomat
10
maybe you should use explicit conversion like
SELECT id, prices, ANY((bigint(x.valid_to) > now()) FOR x IN prices) AS cond FROM de_de_products where cond = 1;
to make sure JSON.field fetched into the right type of the numeric value and does not overflow the int type?
@tomat
that works. Thanks for the tip!