Many to many relationships in elasticsearch

Question

I know that in elasticsearch, we can have child/parent relationships between documents.

And then, when indexing, I can pass the parent id so that the child and parent documents are linked:

$ curl -XPUT localhost:9200/blogs/blog_tag/1122?parent=1111 -d '{    "tag" : "something"}'

Is there anyway to model a many to many relationship in elasticsearch?

Data is resides in a MySQL database with the following schema:

account
========
id
name
some_property

group
========
id
name
description

account_group
=============
account_id
group_id
primary_group //This is 1 or 0 depending on whether the group is the primary group for that account.

This is currently my mapping for account (please excuse the array notation, I am using Elastica in PHP to talk to my elasticsearch server):

**Mapping for account**

'name' => array(
    'type' => 'string'),

'some_property' => array(
    'type' => 'string'),

'groups' => array(
   'properties' => array(
    'id'      => array('type' => 'integer'),
    'primary' => array('type' => 'boolean')
    )
),

**Mapping for group**

'name' => array(
        'type' => 'string'),

'description'=> array(
        'type' => 'string')

The problem with this approach is that if a group is deleted from the index, I will need to go through each account and delete the group id from each account. This seems to be a bit inefficient to me. I also presume that this would not be an issue when using elasticsearch's child/parent relationships.

Is there anyway to model many-to-many relationships in elasticsearch?

score 12 · Accepted Answer · answered Aug 10 '12 at 09:07

There's no way to model many-to-many relationships.

The only way is to store the id of each group in each account like I have done so above.

Elasticsearch is pretty efficient, so often times, reindexing is an acceptable solution. Also, elasticsearch has the notion of documents and is not a relational storage system, so many-to-many relationships would probably never be implemented.

score 0 · Answer 2 · answered Nov 14 '13 at 00:43

When you think of efficiency, what you need to consider is write-time vs read-time efficiency. Relational databases favour write-time efficiency, while NoSQL favours read-time efficiency.

You need to carefully consider the ratio of read vs write in your application, and determine what will be more efficient overall. In the end, something needs to do the work of joining all the relationships, either when the data is written, or when the data is read.

Many to many relationships in elasticsearch

2 Answers2

Linked