Help with Filtering values in a Subquery with Manticore

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!

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 :slight_smile:

it works. :slight_smile:

is it also possible to filter by values? for example “2 cm”

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.