Querying nested objects with same id and status

[Marko_Muric]

Hi everyone, this is my first post here, and I'll try to describe problem best way I can. If you need any additional info please let me know.

Here is the mapping of my document:

PUT test
{
    "settings" : {
        "number_of_shards" : 3,
        "number_of_replicas" : 2
    },
    "mappings" : {
        "test_doc": {
          "properties": {
            "email": {
                "type": "keyword"
            },
            "name": {
              "type": "keyword"
            },
            "items": {
              "type": "nested",
              "properties": {
                "item_id": {"type": "long"},
                "status": {"type": "keyword"},
                "description": {"type": "keyword"},
                "place": {"type": "keyword"},
                "type": {"type": "keyword"}
              }
            }
          }
        }
    }
}


PUT test/test_doc/1
{
    "user" : "john doe",
    "email" : "j.doe@test.com",
    "items": [
      {
        "item_id": 111,
        "status": "inactive",
        "place": "somewhere north",
        "description": "lorem ipsum",
        "type": "type"
      },
      {
        "item_id": 222,
        "status": "active",
        "place": "somewhere north",
        "description": "lorem ipsum",
        "type": "type"
      },
      {
        "item_id": 111,
        "status": "active",
        "place": "somewhere north",
        "description": "lorem ipsum",
        "type": "type"
      }
    ]
}

PUT test/test_doc/2
{
    "user" : "name namson",
    "email" : "n.nameson@test.com",
    "items": [
      {
        "item_id": 111,
        "status": "inactive",
        "place": "somewhere north",
        "description": "lorem ipsum",
        "type": "type"
      },
      {
        "item_id": 222,
        "status": "inactive",
        "place": "somewhere north",
        "description": "lorem ipsum",
        "type": "type"
      }
    ]
}

PUT test/test_doc/3
{
    "user" : "no name",
    "email" : "n.name@test.com",
    "items": [
      {
        "item_id": 111,
        "status": "inactive",
        "place": "somewhere north",
        "description": "lorem ipsum",
        "type": "type"
      },
      {
        "item_id": 222,
        "status": "active",
        "place": "somewhere north",
        "description": "lorem ipsum",
        "type": "type"
      }
    ]
}

I'm using following query to get data that I need.

GET /test/_search
{
  "query": {
    "bool": {
      "filter": [{
        "nested": {
          "path": "items",
          "query": {
            "bool": {
              "must": [
                {
                  "match": {
                    "items.item_id": 111
                  }
                }
              ]
            }
          }
        }
      }]
    }
  }
}

This is mainly because I can append any match filter that I need in "must", and get for example all users that have nested objects with item_id: 111 and type: "type2".

And now we are getting to the problem. I'm trying to filter results to return users that have active or inactive items, for particular item_id. To get users with active items is not a problem, I just need to append "match": { "items.status": "active" } in "must".

Problem is with inactive items. I need to get users with inactive items and with item_id, but that this users do not have active items with this same item_id. For example if I filter users with items.item_id: 111 and items.status: inactive, I should get somehow users with id 2 and 3, but not user with id 1.

I tried several different queries, without success including several different versions of following Script Query:

GET /test/_search
{
  "query": {
    "bool": {
      "filter": [{
        "nested": {
          "path": "items",
          "query": {
            "bool": {
              "must": [
                {
                  "match": { 
                    "items.item_id": 111
                  }
                },
                
                {
							    "script": {
                    "script": {
                      "lang": "painless",
                      "source": "boolean tmp = true; for(i in doc['items.status'].values){ tmp = tmp && (i !='active') } return tmp;"
                    }
							    }
                }
              ]
            }
          }
        }
      }]
    }
  }
}

I would appreciate any idea or hint how to get this results.

Thanks in advance