ES query to match all elements in array

[GeorgetaPojoga]

So I got this document with a nested array that I want to filter with this query.

I want ES to return all documents where all items have changes = 0 and that only. If document has even a single item in the list with a change = 1, that's discarded.

Is there any way I can achieve this starting from the query I have already wrote? Or should I use a script instead?

DOCUMENTS:

{
    "id": "abc",
    "_source" : {
        "trips" : [
            {
                "type" : "home",
                "changes" : 0
            },
            {
                "type" : "home",
                "changes" : 1
            }
        ]
    }
},
{
        "id": "def",
        "_source" : {
            "trips" : [
                {
                    "type" : "home",
                    "changes" : 0
                },
                {
                    "type" : "home",
                    "changes" : 0
                }
            ]
        }
    }

QUERY:

GET trips_solutions/_search

    {
      "query": {
        "bool": {
          "must": [
            {
              "term": {
                "id": {
                  "value": "abc"
                }
              }
            },
            {
              "nested": {
                "path": "trips",
                "query": {
                  "range": {
                    "trips.changes": {
                      "gt": -1,
                      "lt": 1
                    }
                  }
                }
              }
            }
          ]
        }
      }
    }

EXPECTED RESULT:

{
            "id": "def",
            "_source" : {
                "trips" : [
                    {
                        "type" : "home",
                        "changes" : 0
                    },
                    {
                        "type" : "home",
                        "changes" : 0
                    }
                ]
            }
        }

Elasticsearch version: 7.6.2

Already read this answers but they didn't help me: How to match all item in nested array ElasticSearch: How to query exact nested array

[spinscale (Alexander Reelsen)]

I might be missing something, but I don't think there is a proper query to make this work. I do have an idea though by using an ingest pipeline and another query by writing out a property if all the changes are unique.

DELETE test

POST _ingest/pipeline/_simulate?error_trace=true
{
  "docs": [
    {
      "_source": {
        "value": "abc",
        "trips": [
          {
            "type": "home",
            "changes": 0
          },
          {
            "type": "home",
            "changes": 1
          }
        ]
      }
    },
    {
      "_source": {
        "value": "abc",
        "trips": [
          {
            "type": "home",
            "changes": 0
          },
          {
            "type": "home",
            "changes": 0
          }
        ]
      }
    }
  ],
  "pipeline": {
    "processors": [
      { "script": {
        "lang": "painless",
        "source": "def changes = ctx.trips.stream().map(t -> t.changes).collect(Collectors.toSet()); if (changes.size() == 1) { ctx.trips_unique = changes.iterator().next(); }"
      }}
    ]
  }
}

PUT _ingest/pipeline/trips_pipeline
{
  "processors": [
    {
      "script": {
        "lang": "painless",
        "source": "def changes = ctx.trips.stream().map(t -> t.changes).collect(Collectors.toSet()); if (changes.size() == 1) { ctx.trips_unique = changes.iterator().next(); }"
      }
    }
  ]
}

PUT test 
{
  "settings": {
    "index.default_pipeline" : "trips_pipeline"
  }, 
  "mappings": {
    "properties": {
      "trips" : {
        "type": "nested"
      }
    }
  }
}

POST test/_doc
{
  "value": "abc",
  "trips": [
    {
      "type": "home",
      "changes": 0
    },
    {
      "type": "home",
      "changes": 1
    }
  ]
}

POST test/_doc
{
  "value": "abc",
  "trips": [
    {
      "type": "home",
      "changes": 0
    },
    {
      "type": "home",
      "changes": 0
    }
  ]
}

POST test/_refresh

GET test/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "value.keyword": "abc"
          }
        },
        {
          "term": {
            "trips_unique": "0"
          }
        }
      ]
    }
  }
}

hope that helps