# Querying nested objects with same id and status

**URL:** <https://discuss.elastic.co/t/querying-nested-objects-with-same-id-and-status/143465>\
**Category:** Elasticsearch\
**Created:** [August 8, 2018, 9:23am UTC](https://discuss.elastic.co/t/querying-nested-objects-with-same-id-and-status/143465 "2018-08-08T09:23:47Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Marko\_Muric](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marko_muric/32/32144_2.png) [@Marko\_Muric](https://discuss.elastic.co/u/Marko_Muric)\
**Post date:** [August 8, 2018, 9:23am UTC](https://discuss.elastic.co/t/querying-nested-objects-with-same-id-and-status/143465/1 "2018-08-08T09:23:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 5, 2018, 9:23am UTC](https://discuss.elastic.co/t/querying-nested-objects-with-same-id-and-status/143465/2 "2018-09-05T09:23:50Z")

</div>

Closed on September 5, 2018, 9:23am UTC

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.