Complex filter in Opensearch

OpenSearch Dashboards
1

Versions (relevant - OpenSearch/Dashboard/Server OS/Browser):
Opensearch Dashboard 2.13

Describe the issue:
Hello,
I am looking for some help with trying to create a filter in a dashboard and I am afraid that DQL is not advanced enough to help me with this.

For example, I have a set of records with users and permissions. Each record contains a set of 1 user and 1 permission. Basically, one record tells me that a user is assigned to a permission.
Obviously, I can have another record with the same user but a different permission (or the same permission and a different user)

So, I want a filter something similar to this: I want to retrieve all records where the same user has permissions X and Y. In SQL it would be something like this:

select * from table where permission = ‘X’ and exists (select 1 from table where permission = ‘Y’ and user_name = table.user_name);
Any ideas if this can be done in Opensearch?
Thanks
Alexandru

1 Like
2

@ciuline Did you consider using DLS?

3

Hello,
Yes, that’s what I am trying to use, but I don’t know how I could translate that sql query I mentioned to DLS. The sql query joins a table to the same table and I couldn’t find a way, at the moment, to recreate this in DLS.
Thanks

4

@ciuline You could avoid SQL join by creating an alias and assigning it to more than one indices. Querying alias will query all related indices.