I am querying data from a Mongo db collection using a MongoDB connector in Qlik Sense that has worked successfully for all of my queries until now. I want to query a table where column_1
matches each string value in a list of 30-100 string values generated by a for loop.
The SQL query I want to perform is below. Note that Qlik Sense uses a SQL wrapper to extract data from ODBC:
Qlik doc: Filtering with ODBC data
[table_1]
LOAD *;
SELECT [column_1]
FROM db
WHERE column_1 IN ('value_x-1', 'value_y-5', 'value_x-3');
However, I get this error:
ERROR [HY000] [Qlik][MongoDBODBC] (110) Error from MongoDB Client: Badly formed input data (Error Code: 9)
The query works if I filter a numeric column (e.g., WHERE column_2 = 0) or using “WHERE … LIKE” and that worked for one, but it is not a good solution for multiple strings.
I also tried
WHERE column_1 = ('value_x-1', 'value_y-5', 'value_x-3')
, and got this error:
ERROR [42000] [Qlik][SQLEngine] (31500) Row value constructor not supported in predicates such as quantified comparisons, IN, BETWEEN.
I am not experienced with Mongo or other databases, but clearly I am not formatting the query correctly. I haven’t found documentation explaining how the SQL wrapper works for MongoDB in Qlik Sense. I see that WHERE … IN … is supported. Perhaps it is this combination of SQL with MQL in the arguments: Document Relational Definition Language.
Questions:
- Do you know how to format this query after the IN operator?
- Is there documentation for querying Mongo using BI tool SQL wrappers?
- If this is not possible, is there an alternative way to query the data beyond where … (like|in|etc.)…?