CODEWITHSUNDEEP
elasticsearch
PrestonDocks
PrestonDocks

Reputation: 5408

elasticsearch - query between document types

I have a production_order document_type

i.e. 

{
  part_number: "abc123",
  start_date: "2018-01-20"
},
{
  part_number: "1234",
  start_date: "2018-04-16"
}

I want to create a commodity document type i.e.

{
  part_number: "abc123",
  commodity: "1 meter machining"
},
{
  part_number: "1234",
  commodity: "small flat & form"
}

Production orders are datawarehoused every week and are immutable.

Commodities on the other hand could change over time. i.e abc123 could change from 1 meter machining to 5 meter machining, so I don't want to store this data with the production_order records.

If a user searches for "small flat & form" in the commodity document type, I want to pull all matching records from the production_order document type, the match being between part number.

Obviously I can do this in a relational database with a join. Is it possible to do the same in elasticsearch?

If it helps, we have about 500k part numbers that will be commoditized and our production order data warehouse currently holds 20 million records.

Upvotes: 0

Views: 131

Answers (2)

PrestonDocks
PrestonDocks

Reputation: 5408

I have found that you can indeed now query between indexs in elasticsearch, however you have to ensure your data stored correctly. Here is an example from the 6.3 elasticsearch docs

Terms lookup twitter example At first we index the information for user with id 2, specifically, its followers, then index a tweet from user with id 1. Finally we search on all the tweets that match the followers of user 2.

PUT /users/user/2
{
    "followers" : ["1", "3"]
}

PUT /tweets/tweet/1
{
    "user" : "1"
}

GET /tweets/_search
{
    "query" : {
        "terms" : {
            "user" : {
                "index" : "users",
                "type" : "user",
                "id" : "2",
                "path" : "followers"
            }
        }
    }
}

Here is the link to the original page

https://www.elastic.co/guide/en/elasticsearch/reference/6.1/query-dsl-terms-query.html

In my case above I need to setup my storage so that commodity is a field and it's values are an array of part numbers. i.e.

{
  "1 meter machining": ["abc1234", "1234"]
}

I can then look up the 1 meter machining part numbers against my production_order documents

I have tested and it works.

Upvotes: 1

Mallikarjuna J S
Mallikarjuna J S

Reputation: 130

There is no joins supported in elasticsearch.

You can query twice first by getting all the partnumbers using "small flat & form" and then using all the partnumbers to query the other index.

Else try to find a way to merge these into a single index. That would be better. Updating the Commodities would not cause you any problem by combining the both.

Upvotes: 0

Related Questions