Jean Jean - 4 months ago 14
SQL Question

Access array elements ANSI SQL

I'm using Drill to query MongoDB using ANSI SQL , I have a field that contains an array of values, I want to be able to access those elements to join them with other documents .

select name from table where table.id = array.element;


but other than
FLATTEN
which divides them into multiple lines, I can't access the array's elements.
Any help please ?

Answer

I added some sample data in mongodb

db.col.insert({"id":1,name:"dev","arr":[1,2,3,4]});

Working query from Drill:

select name from col4 where id=arr[0];

Output:

+-------+
| name  |
+-------+
| dev   |
+-------+