Executing SQL via PPL

Is it possible to execute SQL via PPL? I’m asking because it seems it may overcome some JOINs limitations. WDYT?
Sadly, seems like JOIN in SQL disables “filter” feature.

Sadly, seems like JOIN in SQL disables “filter” feature.
Right. It seems only Default and Aggregate QueryActions checks “filter” but not joins ones.

I am not sure if these are the right answer but using “must” is similar to AND, “must_not” similar to NOT, “should” is similar to OR to create JOIN condition

The short answer is “yes”, you can use SQL via PPL.

Here is a join example:

POST /_plugins/_sql?format=json
“query” : “SELECT pla.login FROM asset_physical ap join login_access pla on pla.corp_cd = ap.corp_cd AND pla.cli_no = ap.cli_no”

The SQL is a subset of ANSI, so be aware that there are some drawbacks. Here are the constraints for joins:

@JoelF thanks for answering.

“yes”, you can use SQL via PPL.

I just get to workbench and run


But I can’t manage how to hook up sql in PPL

source=select * from idxa
sql=select * from idxa 

Neither works.

The example which I used above came from the OpenSearch Dashboard (DevTools) Console. There is also a Query Workbench available, to just put in the plain SQL.

Here is a curl example:

curl --location ‘https://URL INSERTED HERE/_plugins/_sql?format=json’
–header ‘Content-Type: application/json’
–header ‘Authorization: Basic encrypted password
–data '{
“query” : “SELECT * FROM login_profile lp join login_access pla on pla.login = lp.login where lp.login = ‘'‘XXXXXXXX’'’”