1

Our table has a nested map roles like this:

{group: 123, roles: {boss: department1, manager: department2}}

Now I want to find all documents in group 123 who has a role in department1. How can I filter based on the value field of the nested map? Something like:

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "group": "123"
          }
        },
        {
          "nested": {
            "path": "roles",
            "query": {
              "match": {
                "roles.???": "department1"
              }
            }
          }
        }
      ]
    }
  }
}

I know how to filter by nested object keys but could not find out filtering by "object value".

Nikolay Vasiliev
  • 5,656
  • 22
  • 31
Ali Khosro
  • 1,580
  • 18
  • 25

1 Answers1

1

Inspired by this answer, you can script your way to a solution:

{
  "min_score": 0.1,
  "query": {
    "function_score": {
      "query": {
        "match": {
          "group": "123"
        }
      },
      "functions": [
        {
          "script_score": {
            "script": {
              "source": """
                for (def role : params["_source"]["roles"].values()) {
                  if (role == params.role) {
                    return 1
                  }
                }
                
                return 0;
              """,
              "params": {
                "role": "department1"
              }
            }
          }
        }
      ]
    }
  }
}

Beware of the inefficiencies of scripting though -- it may get really slow.

I'd instead advise to rethink your structure -- keep the nested-ness but go with

{
  "group": 123,
  "roles": [
    {
      "position": "manager",
      "department": "department1"
    },
    {
      "position": "boss",
      "department": "department2"
    }
  ]
}
Joe - GMapsBook.com
  • 15,787
  • 4
  • 23
  • 68
  • Thank you. I was hoping for easier solution. We thought about your suggestion. There is a downside to that structure: it is not Cassandra friendly. In Cassandra, we have to define the column type as a list of frozen objects, which makes working with them (from cassandra) clumsy. We will try your first solution, if it was too slow, we might just fetch all records (in group 123) and filter them in application layer. – Ali Khosro Aug 07 '20 at 21:45