Elasticsearch multiple index query

Question

I have a following index which stores the course details (I have truncated some attributes for brevity):

{
  "settings": {
    "index": {
      "number_of_replicas": "1",
      "number_of_shards": "1"
    }
  },
  "aliases": {
    "course": {
      
    }
  },
  "mappings": {
    "properties": {
      "name": {
        "type": "text"
      },
      "id": {
        "type": "integer"
      },
      "max_per_user": {
        "type": "integer"
      }
    }
  }
}

Here max_per_user is number of times a user can complete the course. A user is allowed through a course multiple times but not more than max_per_user for a course I want to track user interactions with courses. I have created following index to track interaction events. event_type_id represents a type of interaction

{
  "settings": {
    "index": {
      "number_of_replicas": "1",
      "number_of_shards": "1"
    }
  },
  "aliases": {
    "course_events": {
      
    }
  },
  "mappings": {
    "properties": {
      "user_progress": {
        "dynamic": "true",
        "properties": {
          "current_count": {
            "type": "integer"
          },
          "user_id": {
            "type": "integer"
          },
          "events": {
            "dynamic": "true",
            "properties": {
              "event_type_id": {
                "type": "integer"
              },
              "event_timestamp": {
                "type": "date",
                "format": "strict_date_time"
              }
            }
          }
        }
      },
      "created_at": {
        "type": "date",
        "format": "strict_date_time"
      },
      "course_id": {
        "type": "integer"
      }
    }
  }
}

Where current_count is number of times the user has gone through the complete course

Now when I run a search on course index, I also want to be able to pass in the user_id and get only those courses where the current_count for the given user is less than max_per_user for the course

My search query for course index is something like this (truncated some filters for brevity). This query is executed when a user searches for a course, so basically at the time of executing this I will have user_id.

{
  "sort": [
    {
      "id": "desc"
    }
  ],
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "end_date": {
              "gte": "2020-09-28T12:27:55.884Z"
            }
          }
        },
        {
          "range": {
            "start_date": {
              "lte": "2020-09-28T12:27:55.884Z"
            }
          }
        }
      ],
      "must": [
        {
          "term": {
            "is_active": true
          }
        }
      ]
    }
  }
}

I am not sure how to construct my search query such that I am able to filter out courses where max_per_user has been achieved for a given user_id.

Answer 1

If I understood the question correctly you want to find the courses where max_per_user limit isn't exceeded. My answer is on the same basis:

Considering your current Schema way to find what you want is:

1. For the given user_id find all the course_ids and their corresponding completion count
2. Using the data fetched in #1 find out the courses where-in max_per_user limit is not exceeded.

Now comes the problem:

1. In a relational database such use case can be solved using table join and checks
2. Elastic Search doesn't support joins and can't be done here.

Poor solution with current schema:

1. For each course check whether it is applicable or not. For n courses number of queries to E.S will be proportional to N.

Solution with current schema:

1. With-in the user-course-completion index (second index you mentioned), track max_per_user as well and use a simple query like below, to get the required course ids :

    {
     "size": 10,
     "query": {
       "script": {
          "script": "doc['current_usage'].value<doc['max_per_user'].value && 
          doc['u_id'].value==1" // <======= 1 is the user_id here
   }
   }
   }