I have the following 2 *.csv files. And they have a common column called CustomerID.

Orders.csv

OrderID,CustomerID,OrderDate
10308,2,1996/9/18
10309,37,1996/9/19
10310,77,1996/9/20

Customers.csv

CustomerID,CustomerName,ContactName,Country,CustomerCreateDate
1,Alfreds Futterkiste,Maria Anders,Germany,2022/4/1
2,Ana Trujillo Emparedados y helados,Ana Trujillo,Mexico,2022/5/3
3,Antonio Moreno Taquería,Antonio Moreno,Mexico,2022/4/23

I imported them to my elasticsearch as 2 indices (which are named as index_customers, and index_orders) using "Upload a file" integration.

Is it possible to use Query DSL to fetch the documents that contain CustomerID=2 in the 2 indices?

Here's what I have tried:

GET index_*/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "CustomerName": {
              "query": "Ana Trujillo Emparedados y helados"
            }
          }
        },
        {
          "match": {
            "OrderID": {
              "query": "10308"
            }
          }
        }
      ]
    }
  }
}

But it doesn't return any matched data.

If you think of the 2 *.csv files as 2 tables in MySQL, this is what I want to query.

SELECT Customers.CustomerID, Customers.CustomerName, Customers.ContactName, Customers.Country, Customers.CustomerCreateDate, Orders.OrderID,  Orders.OrderDate
FROM Customers 
INNER JOIN Orders
ON Orders.CustomerID=Customers.CustomerID WHERE Customers.CustomerID=2;

Is it possible to perform this kind of query in elasticsearch?

I've read this document, but I haven't figured out how to use nested, has_child, and has_parent queries to implement what I want.

CodePudding user response：

Tldr;

Elasticsearch is not made for Join queries.

Workaround

GET 73048773_*/_search
{
  "query": {
    "match": {
      "CustomerID": 2
    }
  }
  ,
  "_source": ["CustomerName", "OrderID"]
}

Should give you:

{
 ...
    "hits": [
      {
        "_index": "73048773_customer",
        "_id": "_Rr5GoIBc5_pveXGq7kJ",
        "_score": 1,
        "_source": {
          "CustomerName": "Ana Trujillo Emparedados y helados"
        }
      },
      {
        "_index": "73048773_order",
        "_id": "-Rr5GoIBc5_pveXGq7kJ",
        "_score": 1,
        "_source": {
          "OrderID": 10308
        }
      }
    ]
  }
}