elasticsearch nest index relational database

Question

I've indexed 2 separate tables into Elasticsearch - Meetings and MeetingAttendees. A one to many relationship - a meeting can have many attendees.

Meetings
ID: 1

ID: 2

Meeting Attendees
MeetingAttendeeID: 1
MeetingID: 1
Name: "tom"

MeetingAttendeeID: 2
MeetingID: 1
Name: "david"

MeetingAttendeeID: 3
MeetingID: 2
Name: "david"

I've tried to create the relationship like this, but I'm not seeing any difference in ES

client.CreateIndex(ci => ci.Index("testmappingindex")
                .AddMapping<Meeting>(m => m.MapFromAttributes())
                .AddMapping<MeetingAttendee>(m => m.MapFromAttributes().SetParent<Meeting>()));

I'd like to be able to query like this:

result = client.Search<Meeting>(s => s
                .Type("Meeting")
                .From(0)
                .Size(10)
                .Query(q => q.MeetingAttendees(ma => ma.Terms(t => t.Name == "david")))
                    )
            ).Documents.ToList();

However, the mapping isn't working, I don't see any request going out in fiddler, and I'm not sure if it did work that this query would return the meetings with David as the attendee.

Answer 1

I'm recommending you Nested Object to handle relationship between Meeting and Attendee. It means we will be storing all data in one document(Meeting).

Meeting and Attendee classes:

public class Meeting
{
    public int Id { get; set; }
    public string Name { get; set; }
    [ElasticProperty(Type = FieldType.Nested)]
    public List<Attendee> MeetingAttendees { get; set; }
}

public class Attendee
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Create index:

var indicesOperationResponse = client.CreateIndex(descriptor => descriptor
    .Index(indexName)
    .AddMapping<Meeting>(m => m.MapFromAttributes()));

Index some data:

var david = new Attendee {Id = 1, Name = "David"};
var carl = new Attendee {Id = 2, Name = "Carl"};
var jason = new Attendee {Id = 3, Name = "Jason"};

client.Index(new Meeting {Id = 1, Name = "Meeting1", MeetingAttendees = new List<Attendee>{david, carl}});
client.Index(new Meeting {Id = 2, Name = "Meeting2", MeetingAttendees = new List<Attendee>{jason}});
client.Index(new Meeting {Id = 3, Name = "Meeting3", MeetingAttendees = new List<Attendee>{jason, david}});

client.Refresh();

We should modify your query just a little bit:

var result = client.Search<Meeting>(s => s
    .From(0)
    .Size(10)
    .Query(q => q.Nested(n => n
        .Path(p => p.MeetingAttendees.First())
        .Query(qq => qq
            .Term(meeting => meeting.OnField(f => f.MeetingAttendees.First().Name).Value("david"))))));

Result from elasticsearch:

{
   "took": 4,
   "timed_out": false,
   "_shards": {
      "total": 5,
      "successful": 5,
      "failed": 0
   },
   "hits": {
      "total": 2,
      "max_score": 1.4054651,
      "hits": [
         {
            "_index": "my_index",
            "_type": "meeting",
            "_id": "1",
            "_score": 1.4054651,
            "_source": {
               "id": 1,
               "name": "Meeting1",
               "meetingAttendees": [
                  {
                     "id": 1,
                     "name": "David"
                  },
                  {
                     "id": 2,
                     "name": "Carl"
                  }
               ]
            }
         },
         {
            "_index": "my_index",
            "_type": "meeting",
            "_id": "3",
            "_score": 1.4054651,
            "_source": {
               "id": 3,
               "name": "Meeting3",
               "meetingAttendees": [
                  {
                     "id": 3,
                     "name": "Jason"
                  },
                  {
                     "id": 1,
                     "name": "David"
                  }
               ]
            }
         }
      ]
   }
}

UPDATE:

In your case where you are going to index more related data it can be worth to have a look on parent-child relationship

Sample classes:

public class Meeting
{
    public int Id { get; set; }
    public string Name { get; set; } 
}

public class Attendee
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Mapping:

var indicesOperationResponse = client.CreateIndex(descriptor => descriptor
    .Index(indexName)
    .AddMapping<Meeting>(m => m.MapFromAttributes())
    .AddMapping<Attendee>(m => m.MapFromAttributes().SetParent<Meeting>()));

Sample data:

var david = new Attendee { Id = 1, Name = "David"};
var carl = new Attendee { Id = 2, Name = "Carl"};
var jason = new Attendee {Id = 3, Name = "Jason"};

client.Index(new Meeting {Id = 1, Name = "Meeting1"});
client.Index(new Meeting {Id = 2, Name = "Meeting2"});
client.Index(new Meeting {Id = 3, Name = "Meeting3"});

client.Index(david, descriptor => descriptor.Parent("1"));
client.Index(carl, descriptor => descriptor.Parent("1"));
client.Index(jason, descriptor => descriptor.Parent("2"));

client.Refresh();

Now, we have to find parent by their children. With NEST you can do this by this query:

var searchResponse = client.Search<Meeting>(s => s
    .Query(q => q
        .HasChild<Attendee>(c => c
            .Query(query => query.Term(t => t.OnField(f => f.Name).Value("david"))))));