Executing SQL via PPL

Plugins SQL
1

Hello,
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.

2

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

3

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

4

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:

5

@JoelF thanks for answering.

“yes”, you can use SQL via PPL.

I just get to workbench and run

source=idxa

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

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

Neither works.

6

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’'’”
}
’

7

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.