ElasticSearch: How to query exact nested array

Question

I am trying to query a certain type of documents in my index.

Let's see the following document:

{
  "id": 1,
  "title": "My first Collection",
  "items": [
    {
      "code": "SB",
      "order": 1,
      "random": "something random"
    },
    {
      "code": "BB",
      "order": 2,
      "random": "something random"
    },
    {
      "code": "FO",
      "order": 3,
      "random": "something random"
    },
    {
      "code": "RA",
      "order": 4,
      "random": "something random"
    },
    {
      "code": "FO",
      "order": 5,
      "random": "something random"
    }
  ]
}

The items field is a nested field.

I would like to query all the articles that matches the exact pattern:

{
  "items": [
    {
      "code": "SB",
      "order": 1
    },
    {
      "code": "BB",
      "order": 2
    },
    {
      "code": "FO",
      "order": 3
    },
    {
      "code": "RA",
      "order": 4
    }
  ]
}

The queried documents would have all those 4 items with this exact combination of fields. But they could have more too. The described document above would match the query.

I searched through the entire documentation, particularly in the nested queries part and I did not find any way to get it working.

Is it even possible or should I implement my own algorithm/script?

EDIT:

I tried the following without success:

{
  "query": {
    "nested": {
      "path": "items",
      "query": {
        "bool": {
          "must": [
            {
              "query_string": {
                "query": "items.code:SB AND items.order:1"
              }
            },
            {
              "query_string": {
                "query": "items.code:BB AND items.order:2"
              }
            },
            {
              "query_string": {
                "query": "items.code:FO AND items.order:3"
              }
            }
          ]
        }
      }
    }
  }
}

score 8 · Accepted Answer · answered Sep 07 '17 at 21:04

This query works:

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "items",
            "query": {
              "bool": {
                "must": [
                  {
                    "term": {
                      "items.code": "SB"
                    }
                  },
                  {
                    "term": {
                      "items.order": "1"
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "items",
            "query": {
              "bool": {
                "must": [
                  {
                    "term": {
                      "items.code": "BB"
                    }
                  },
                  {
                    "term": {
                      "items.order": "2"
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "items",
            "query": {
              "bool": {
                "must": [
                  {
                    "term": {
                      "items.code": "FO"
                    }
                  },
                  {
                    "term": {
                      "items.order": "3"
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

Notice that is basically the one you used, BUT specifying the nested for each sub-query and not overall.

When you use the nested keyword, you are saying to ElasticSearch to try to match inside a nested document, which is separated from the main document. Of course, given your example, an item can't have all the code-order couples "SB"-1, "BB"-2 and "FO"-3 at the same time. But you can requiring them separately with the 3 nested queries.

ElasticSearch: How to query exact nested array

1 Answers1

Linked