# Elasticsearch | Relationships based index

**URL:** <https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058>\
**Category:** Elasticsearch\
**Created:** [April 9, 2019, 3:12pm UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058 "2019-04-09T15:12:18Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nikesh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikesh/32/86579_2.png) [@Nikesh](https://discuss.elastic.co/u/Nikesh)\
**Post date:** [April 9, 2019, 3:12pm UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/1 "2019-04-09T15:12:18Z")

</div>

As asked in this [link](https://discuss.elastic.co/t/elasticsearch-relationship-based-index/171080)  
I need to provide relationship based search.  
Let's consider the same scenario.  
Team and Players would contain similar fields.  
A single team can have multiple players.  
Having a Single Index for this would be something of this structure :

```
 {
 "Team_name" : "xyz",
 "type" : "men",
"Players" : [
       {
      "first" : "John",
      "last" : "Smith",
      "country_ID" : "123"
    },
    {
      "first" : "Alice",
      "last" : "Smith",
      "country_ID" : "679"
    }
]

}

```

I have few doubts over this architecture :

1. Any update to a particular part of " **Players**" array. Let's say **" first" : "John"** is changed to **"first":"Mark"**  
This updating procedure requires reindexing the entire nested document.  
Is there a procedure to Update just the array element to which it belongs to?

2. I would be using **copy\_to** field and search would be based on accessibility and this is done through " **country\_ID**". If a user has access to " **Country\_ID**" :"679" and not to " **123**".  
If a user searches for a string " **Smith**", will " **Smith**" with " **country\_ID**" :" **123**"  
also be searched and highlighted? If so, how do we prevent it from happening?

3. Suppose one player has to be deleted, let's say **Alice Smith** , Is there a procedure to just delete this player or does it require reindexing of this document without details of **Alice Smith**?

---

<div class="post-metadata">

**Author:** ![Nikesh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikesh/32/86579_2.png) [@Nikesh](https://discuss.elastic.co/u/Nikesh)\
**Post date:** [April 9, 2019, 3:13pm UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/2 "2019-04-09T15:13:44Z")

</div>

@dadoonet Apologies for creating new thread as [link](https://discuss.elastic.co/t/elasticsearch-relationship-based-index/171080) has been closed.  
Please provide any suggestions

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [April 9, 2019, 4:10pm UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/3 "2019-04-09T16:10:23Z")

</div>

1. No
2. Not sure I understood but it's easy with Elasticsearch and Kibana dev console to test it.
3. You need to reindex the whole document.

---

<div class="post-metadata">

**Author:** ![Nikesh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikesh/32/86579_2.png) [@Nikesh](https://discuss.elastic.co/u/Nikesh)\
**Post date:** [April 10, 2019, 10:46am UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/4 "2019-04-10T10:46:59Z")

</div>

1. My mapping is defined such that there exists a copy\_to field enabled. This means Nested Object " **Players**" and all the other fields part of this JSON are also part of **copy\_to** field.  
Users might have access to search on only few players. For example, I have access to search only on " **John Smith**" and not " **Alice Smith**". This accessibility is based on " **country\_ID**", which is part of my query through filter. I am using the " **copy\_to**" field to search.  
Incase I search on " **Smith**", I have to make sure it searches only " **Smith**" with " **country\_ID**" :" **123**" and not " **country\_ID**" : " **679** and only the former should be part of the result.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [April 10, 2019, 11:47am UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/5 "2019-04-10T11:47:34Z")

</div>

Could you provide a full recreation script as described in [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

---

<div class="post-metadata">

**Author:** ![Nikesh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikesh/32/86579_2.png) [@Nikesh](https://discuss.elastic.co/u/Nikesh)\
**Post date:** [April 10, 2019, 2:49pm UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/6 "2019-04-10T14:49:02Z")

</div>

Thanks for the response.  
Sure!

This is my mapping :

```
{
  "mappings": {
    "_doc": {
      "properties": {
      	"SINGLE_FIELD": {
                        "type": "text"
                        
                    },
      	"team_name" : {
      		"type": "text",
      		"copy_to": ["SINGLE_FIELD"]
      		
      	},
      "type" : {
      "type": "text",
     "copy_to": ["SINGLE_FIELD"]
      },
        "players": {
          "type": "nested",
          "properties": {
             "first" : {
        			"type" : "text",
        			"copy_to": ["SINGLE_FIELD"]
             },
             "last":{
             	"type" : "text",
             	"copy_to": ["SINGLE_FIELD"]
             },
             "country_ID" :{
             	"type" : "text",
             	"copy_to": ["SINGLE_FIELD"]
             }
          }
        }
        
      }
    }
  }
}

```

And I have indexed one document :

```
{
 "team_name" : "xyz",
 "type" : "men",
"Players" : [
       {
      "first" : "John",
      "last" : "Smith",
      "country_ID" : "123"
    },
    {
      "first" : "Alice",
      "last" : "Smith",
      "country_ID" : "679"
    }
]

}

```

My query is :

```
{
	"query": {

         
		"multi_match": {
            "query":"Smith",
            
            "fields":["SINGLE_FIELD"]
			
		}

	}
} 

```

Now if I as a user has access to only player with **country\_ID** : **679** and not to **123**.  
How do I restrict my search and highlight on **country\_ID** : **123**?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [April 11, 2019, 2:28pm UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/7 "2019-04-11T14:28:41Z")

</div>

Use a bool query.  
Add the `multi_match` within the `must` clause and add a `term` query on `country_ID` field within the `filter` clause.

---

<div class="post-metadata">

**Author:** ![Nikesh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikesh/32/86579_2.png) [@Nikesh](https://discuss.elastic.co/u/Nikesh)\
**Post date:** [April 12, 2019, 4:25am UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/8 "2019-04-12T04:25:47Z")

</div>

thanks for your response.

You meant query structure of the below sort?

```
{
  "query": {
    "bool": {
      "must": [
        {
          "multi_match": {
            "query": "Smith",
            "fields": [
              "SINGLE_FIELD"
            ]
          }
        }
      ],
      "filter": [
        {
          "term": {
            "country_ID": {
              "value": "679"
            }
          }
        }
      ]
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [April 12, 2019, 4:44am UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/9 "2019-04-12T04:44:01Z")

</div>

Yes.

---

<div class="post-metadata">

**Author:** ![Nikesh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikesh/32/86579_2.png) [@Nikesh](https://discuss.elastic.co/u/Nikesh)\
**Post date:** [April 12, 2019, 12:18pm UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/10 "2019-04-12T12:18:52Z")

</div>

Thanks for the response.  
But this is not providing me any output.  
Although the below query without any filter fetches me results -

```
{
  "query": {
    "bool": {
      "must": [
        {
          "multi_match": {
            "query": "Smith",
            "fields": [
              "SINGLE_FIELD"
            ]
          }
        }
      ]
    }
  }
} 

```

Is something wrong with my filter clause?  
this is my filter clause -

```
  "filter": [
    {
      "term": {
        "country_ID": {
          "value": "679"
        }
      }
    }
  ]
```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [April 12, 2019, 12:41pm UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/11 "2019-04-12T12:41:55Z")

</div>

Could you provide a full recreation script as described in [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

---

<div class="post-metadata">

**Author:** ![Nikesh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikesh/32/86579_2.png) [@Nikesh](https://discuss.elastic.co/u/Nikesh)\
**Post date:** [April 15, 2019, 7:31am UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/12 "2019-04-15T07:31:13Z")

</div>

Yes. The below is my query. This does not output any result.

```
   {
	"query": {

          "bool": {"must": [ {
		"multi_match": {
            "query":"Smith",
            
            "fields":["SINGLE_FIELD"]
			
		}
          }
          ],
          
            "filter": [
    {
      "term": {
        "players.country_ID": {
          "value": "679"
        }
      }
    }
  ]
          
          
          }

	}
}
```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [April 15, 2019, 8:27am UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/13 "2019-04-15T08:27:31Z")

</div>

There's no way to reproduce the problem without a full script. Unless someone wants to assemble it from the snippets you provided.  
I'd recommend providing it yourself as described per the link I shared.

---

<div class="post-metadata">

**Author:** ![Nikesh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikesh/32/86579_2.png) [@Nikesh](https://discuss.elastic.co/u/Nikesh)\
**Post date:** [April 15, 2019, 10:31am UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/14 "2019-04-15T10:31:33Z")

</div>

This is my mapping :

```auto
{
  "mappings": {
    "_doc": {
      "properties": {
      	"SINGLE_FIELD": {
                        "type": "text"
                        
                    },
      	"team_name" : {
      		"type": "text",
      		"copy_to": ["SINGLE_FIELD"]
      		
      	},
      "type" : {
      "type": "text",
     "copy_to": ["SINGLE_FIELD"]
      },
        "players": {
          "type": "nested",
          "properties": {
             "first" : {
        			"type" : "text",
        			"copy_to": ["SINGLE_FIELD"]
             },
             "last":{
             	"type" : "text",
             	"copy_to": ["SINGLE_FIELD"]
             },
             "country_ID" :{
             	"type" : "text",
             	"copy_to": ["SINGLE_FIELD"]
             }
          }
        }
        
      }
    }
  }
}

```

And I have indexed one document :

```auto
{
 "team_name" : "xyz",
 "type" : "men",
"Players" : [
       {
      "first" : "John",
      "last" : "Smith",
      "country_ID" : "123"
    },
    {
      "first" : "Alice",
      "last" : "Smith",
      "country_ID" : "679"
    }
]

}

```

The below is my query. This does not output any result.

```auto
   {
	"query": {

          "bool": {"must": [ {
		"multi_match": {
            "query":"Smith",
            
            "fields":["SINGLE_FIELD"]
			
		}
          }
          ],
          
            "filter": [
    {
      "term": {
        "players.country_ID": {
          "value": "679"
        }
      }
    }
  ]
          
          
          }

	}
}

```

Now if I as a user has access to only player with **country\_ID** : **679** and not to **123**.  
How do I restrict my search and highlight on **country\_ID** : **123**?

---

<div class="post-metadata">

**Author:** ![Nikesh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikesh/32/86579_2.png) [@Nikesh](https://discuss.elastic.co/u/Nikesh)\
**Post date:** [April 17, 2019, 6:44am UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/15 "2019-04-17T06:44:07Z")

</div>

@dadoonet Please provide your suggestion

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [April 17, 2019, 2:40pm UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/16 "2019-04-17T14:40:26Z")

</div>

@Nikesh let me paste the documentation I linked to in case you did not read it yet.

> A typical script like this one **can be copied and pasted in Kibana Dev Console** by any reader. It will definitely help to play with your example and provide a fix for your script.  
> If you don't provide it, there is a chance that nobody can help.

**Emphasis** is mine.

Here is a typical script that anyone can use for example:

```auto
DELETE index
PUT index/_doc/1
{
  "foo": "bar"
}
GET index/_search
{
  "query": {
    "match": {
      "foo": "bar"
    }
  }
}

```

So if you need more help, please provide this.

---

<div class="post-metadata">

**Author:** ![Nikesh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikesh/32/86579_2.png) [@Nikesh](https://discuss.elastic.co/u/Nikesh)\
**Post date:** [April 19, 2019, 7:54am UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/17 "2019-04-19T07:54:51Z")

</div>

Okay!

```
**PUT** nestedindex

    {
      "mappings": {
        "_doc": {
          "properties": {
          	"SINGLE_FIELD": {
                            "type": "text"
                            
                        },
          	"team_name" : {
          		"type": "text",
          		"copy_to": ["SINGLE_FIELD"]
          		
          	},
          "type" : {
          "type": "text",
         "copy_to": ["SINGLE_FIELD"]
          },
            "players": {
              "type": "nested",
              "properties": {
                 "first" : {
            			"type" : "text",
            			"copy_to": ["SINGLE_FIELD"]
                 },
                 "last":{
                 	"type" : "text",
                 	"copy_to": ["SINGLE_FIELD"]
                 },
                 "country_ID" :{
                 	"type" : "text",
                 	"copy_to": ["SINGLE_FIELD"]
                 }
              }
            }
            
          }
        }
      }
    }

**POST** nestedindex/_doc/1

    {
     "team_name" : "xyz",
     "type" : "men",
    "Players" : [
           {
          "first" : "John",
          "last" : "Smith",
          "country_ID" : "123"
        },
        {
          "first" : "Alice",
          "last" : "Smith",
          "country_ID" : "679"
        }
    ]

    }

**GET** nestedindex/_search

 {
	"query": {

          "bool": {"must": [ {
		"multi_match": {
            "query":"Smith",
            
            "fields":["SINGLE_FIELD"]
			
		}
          }
          ],
          
            "filter": [
    {
      "term": {
        "players.country_ID": {
          "value": "679"
        }
      }
    }
  ]
          
          
          }

	}
}
```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [April 26, 2019, 9:06am UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/18 "2019-04-26T09:06:54Z")

</div>

Your example does not work.

```auto
GET nestedindex/_search
{
  "query": {
    "match": {
      "SINGLE_FIELD": "Smith"
    }
  }
}

```

Probably because of a typo with `Players` vs `players`.

I changed your "script" by removing all the non needed characters `**`, removing blank lines, ...  
I also added a `nested` query to make your initial query works.

Not sure what you are looking for then.

```auto
DELETE nestedindex
PUT nestedindex
{
  "mappings": {
    "_doc": {
      "properties": {
        "SINGLE_FIELD": {
          "type": "text"
        },
        "team_name": {
          "type": "text",
          "copy_to": [
            "SINGLE_FIELD"
          ]
        },
        "type": {
          "type": "text",
          "copy_to": [
            "SINGLE_FIELD"
          ]
        },
        "players": {
          "type": "nested",
          "properties": {
            "first": {
              "type": "text",
              "copy_to": [
                "SINGLE_FIELD"
              ]
            },
            "last": {
              "type": "text",
              "copy_to": [
                "SINGLE_FIELD"
              ]
            },
            "country_ID": {
              "type": "text",
              "copy_to": [
                "SINGLE_FIELD"
              ]
            }
          }
        }
      }
    }
  }
}
POST nestedindex/_doc/1
{
  "team_name": "xyz",
  "type": "men",
  "players": [
    {
      "first": "John",
      "last": "Smith",
      "country_ID": "123"
    },
    {
      "first": "Alice",
      "last": "Smith",
      "country_ID": "679"
    }
  ]
}
GET nestedindex/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "multi_match": {
            "query": "Smith",
            "fields": [
              "SINGLE_FIELD"
            ]
          }
        }
      ],
      "filter": [
        {
          "nested": {
            "path": "players",
            "query": {
              "term": {
                "players.country_ID": {
                  "value": "679"
                }
              }
            }
          }
        }
      ]
    }
  }
}

```

---

<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 24, 2019, 9:07am UTC](https://discuss.elastic.co/t/elasticsearch-relationships-based-index/176058/19 "2019-05-24T09:07:04Z")

</div>

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