I am not getting expected result using subquery in _sql method of elasticsearch query DSL

Elastic Stack Elasticsearch
1

Below is the query & output, kindly suggest,

GET _sql?format=txt
{
 "query": """
  SELECT 
  tenant_id,
  (SELECT is_vulnerability_scan_tool_active from "index1") AS VST
  from "index2" where tenant_id='ABCDEFHG'
"""
}

Output :

{
  "error": {
    "root_cause": [
      {
        "type": "planning_exception",
        "reason": """Found 1 problem
line 4:3: Unresolved expression"""
      }
    ],
    "type": "planning_exception",
    "reason": """Found 1 problem
line 4:3: Unresolved expression"""
  },
  "status": 400
}

basically I want to extract data using subquery on the basis on different conditions applied as well as aggregation applied on some column fields.

2

Please don't post images of text as they are hard to read, may not display correctly for everyone, and are not searchable.

Instead, paste the text and format it with </> icon or pairs of triple backticks (```), and check the preview window to make sure it's properly formatted before posting it. This makes it more likely that your question will receive a useful answer.

It would be great if you could update your post to solve this.

1 Like
3

Thanks for suggestion @dadoonet

4

I'm wondering if you are hitting this limitation : SQL Limitations | Elasticsearch Guide [8.17] | Elastic

I think that ES|QL might be able to solve such use case with the coming 8.18 version.

1 Like