Elasticsearch Query: Equal Values of Different Types

[AloysiusParedes]

Hi, new to ELK, and I am ready to start making search queries.

Here is my data (anonymized) and they are all on the same index:

{
    "type": "typeA"
    "fieldA1": "valueA1"
    "fieldA2": "valueA2"
}
{
    "type": "typeB"
    "fieldB1": "valueB1"
    "fieldB2": "valueB2"
}

I am wondering if there is a way to do the query similar to the following SQL Query:

SELECT COUNT(b.field1)
FROM typeA a, typeB b
WHERE a.fieldA1 = b.fieldB1
AND a.fieldA2 = "valueA2"
AND b.fieldB2 = "valueB2"

I have read from other resources that scripts are the way to go. Any suggestions?

Here is what I have tried:

GET /myIndex/_search
{
  "size": 0,
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              { "match": { "type": "typeA"} },
              { "match": { "fieldA2": "valueA2" } }
            ]
          }
        },
        {
          "bool": {
            "must": [
              { "match": { "type": "typeB" } },
              { "match": { "fieldB2": "valueB2" } }
            ]
          }
        }
      ] 
    }
  }
}

But I don't know what the next steps are for getting the

WHERE a.fieldA1 = b.fieldB1

part of the query.

[mayya (Mayya Sharipova)]

There is no query in ES that can do a.fieldA1 = b.fieldB1 on your flat independent documents. Each document is examined independently if it matches a query or not, and there is no connection to other documents.

Some possible options you may look into:

• joints, changing the structure of your documents
• Terms query with lookup, with a limitation that a lookup path is limited to a single doc.