How to handle and filter by deep json arrays?

General Discussions - English
1

Hi, I was trying to filter docs by a json field called “rooms” in a table “hotel” as below:
[
{
“id”: 1,
“smoke”: 1,
“bed_sizes”: [
2,3,4
]
},
{
“id”: 2,
“smoke”: 0,
“bed_sizes”: [
1,2,3
]
}
]

Now I would like to filter hotels that both accepts smoke, and has a bed size of 2. A request as below is similar to what I want:
SELECT id, ANY(r.smoke = 1 AND ANY(r.bed_sizes) = 2 for r in rooms) AS r FROM hotel WHERE r = 1

But clearly this sql does not work. I have read through the manticore and sphinxQL document and found nothing that I can do. I would really appreciate If you can give some advices on above situation. Thanks

2 
mysql> drop table if exists t; create table t(rooms json); insert into t(rooms) values('{ "id": 1, "smoke": 1, "bed_sizes": [ 2,3,4 ] }'),('{ "id": 2, "smoke": 0, "bed_sizes": [ 1,2,3 ] }'); select *, rooms.smoke=1 and any(x=2 for x in rooms.bed_sizes) cond from t where cond = 1;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create table t(rooms json)
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t(rooms) values('{ "id": 1, "smoke": 1, "bed_sizes": [ 2,3,4 ] }'),('{ "id": 2, "smoke": 0, "bed_sizes": [ 1,2,3 ] }')
--------------

Query OK, 2 rows affected (0.01 sec)

--------------
select *, rooms.smoke=1 and any(x=2 for x in rooms.bed_sizes) cond from t where cond = 1
--------------

+---------------------+----------------------------------------+------+
| id                  | rooms                                  | cond |
+---------------------+----------------------------------------+------+
| 1516026267709734924 | {"id":1,"smoke":1,"bed_sizes":[2,3,4]} |    1 |
+---------------------+----------------------------------------+------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---
1 Like
3

Thank you so much for the demo. I made the rooms field itself an json array with brackets [] at the start and end, which makes the filtering not working at all. Now it works perfectly :slight_smile: