# Elastic search complex query

**URL:** <https://discuss.elastic.co/t/elastic-search-complex-query/301399>\
**Category:** Elasticsearch\
**Created:** [April 2, 2022, 7:47pm UTC](https://discuss.elastic.co/t/elastic-search-complex-query/301399 "2022-04-02T19:47:19Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Hassan\_Shaitou](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hassan_shaitou/32/103867_2.png) [@Hassan\_Shaitou](https://discuss.elastic.co/u/Hassan_Shaitou)\
**Post date:** [April 2, 2022, 7:47pm UTC](https://discuss.elastic.co/t/elastic-search-complex-query/301399/1 "2022-04-02T19:47:19Z")

</div>

So, let us say I have 2 indices users and products.

Within users I have the following format:  
users:  
{  
uid:"00001",  
name:"Hash",  
country:"US"  
},

Within the products I have the following format:

product:  
{  
productname: "Shoes",  
.  
.  
.  
uid:"0001"  
}

My question is, how I can link these 2 indices and join its data together? like in MySQL I would do the following:

```auto
SELECT * FROM users u JOIN products p ON u.uid = p.uid;

```

Is there anything similar in Elasticsearch query? please help!

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [April 2, 2022, 8:14pm UTC](https://discuss.elastic.co/t/elastic-search-complex-query/301399/2 "2022-04-02T20:14:51Z")

</div>

Have a look at the approach recommended in [this thread](https://discuss.elastic.co/t/reference-tables-in-elastic/301369). Elasticsearch does not support joins so this is generally does by enriching at index time rather than joining at query time.

---

<div class="post-metadata">

**Author:** ![Hassan\_Shaitou](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hassan_shaitou/32/103867_2.png) [@Hassan\_Shaitou](https://discuss.elastic.co/u/Hassan_Shaitou)\
**Post date:** [April 3, 2022, 1:15pm UTC](https://discuss.elastic.co/t/elastic-search-complex-query/301399/3 "2022-04-03T13:15:36Z")

</div>

Ok, got the point behind the enricher, but the thing is the enricher is one time only, i.e. if an already existing product let's say has the userinfo and then that userinfo got updated, the product won't get updated !

e.g

```auto
users:
{
"uid": "1",
"name": "shop1"
}

```

products index after enriching:

```auto
{
"productname": "shoes",
"uid: "1",
"user": {
   "uid": "1",
    "name": "shop1"
 }
}

```

now if I updated name of shop1 to shop2 in users index then it won't get updated in products index !

@Christian_Dahlqvist

---

<div class="post-metadata">

**Author:** ![Hassan\_Shaitou](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hassan_shaitou/32/103867_2.png) [@Hassan\_Shaitou](https://discuss.elastic.co/u/Hassan_Shaitou)\
**Post date:** [April 3, 2022, 1:19pm UTC](https://discuss.elastic.co/t/elastic-search-complex-query/301399/4 "2022-04-03T13:19:07Z")

</div>

@Christian_Dahlqvist tried enriching on GET request, but couldn't, would like to have something like that if possible !

```auto
GET /products/_search?pipeline=TEST_MAP_USERS_PRODUCTS
{
  "query": {
    "match": {
      "uid": "76KQRuPLXhcPLxKX4m1gOxnTI5U2"
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [April 3, 2022, 1:57pm UTC](https://discuss.elastic.co/t/elastic-search-complex-query/301399/5 "2022-04-03T13:57:40Z")

</div>

That does not exist. Enrichment need to be done at index time.

---

<div class="post-metadata">

**Author:** ![Hassan\_Shaitou](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hassan_shaitou/32/103867_2.png) [@Hassan\_Shaitou](https://discuss.elastic.co/u/Hassan_Shaitou)\
**Post date:** [April 3, 2022, 2:15pm UTC](https://discuss.elastic.co/t/elastic-search-complex-query/301399/6 "2022-04-03T14:15:03Z")

</div>

Got that, but after some googling I got that I need to reindex every time I update the shopname like I said above for example! Is there a better alternative?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [May 1, 2022, 2:15pm UTC](https://discuss.elastic.co/t/elastic-search-complex-query/301399/7 "2022-05-01T14:15:39Z")

</div>

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