Nested SQL Query or Join within an index

Elastic Stack Elasticsearch
1

Hi All,

I couldn't create a visualization/report for the following scenario in Kibana.

We have docs added to an Index as given below. Here one batch (in example below: 20220809.000018), can have many fields like Supplier Name, Invoice Number, PO Number etc. The fields comes as keyName in below json and verifyValue is the value for the field. We have a requirement wherein, user should be able to filter the fields based on the Supplier Name or generate a report from Kibana for a particular supplier.

Given the below index structure, if I filter based on Supplier Name= ABC Systems Inc, Kibana terms visualization/discover will list only those documents which has keyName as supplier name and not all the fields like Invoice Number and PO Number of all those batches which has Supplier Name =ABC Systems Inc.

Is it possible to perform a JOIN query in Kibana to get all the batches with all the fields where Supplier Name ="ABC Systems Inc"

Is this query possible?
Select * from InvoiceStats where batch IN (Select batch from Invoicestats where verifyValue="ABC SYSTEMS INC" and keyName="Supplier Name")

{
    "application": "P2P",
    "batch": "20220809.000018",
    "doctype": "Invoice",
    "keyName": "Supplier Name",
    "verifyValue": "ABC SYSTEMS INC",
	"timestamp": 1660047598651    
  }
  
  {
    "application": "P2P",
    "batch": "20220809.000018",
    "doctype": "Invoice",
    "keyName": "Invoice Number",
    "verifyValue": "345634",
	"timestamp": 1660047598651    
  }
  
  {
    "application": "P2P",
    "batch": "20220809.000018",
    "doctype": "Invoice",
    "keyName": "PO Number",
    "verifyValue": "PO12345",
	"timestamp": 1660047598651    
  }

Thanks,
Geetha

2

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