Search in object array with multiple conditions

[Trino]

Hi,

I hope you can help me to find the solution, my problem is:

I have a list of documents indexed in my index like this:

{
 "title" : "document 1",
 "status" 2,
 "documentParts" : [{ "partTitle": "part 1",
					  "typePart" : 1,
					  "statusPart" : 2},
					{ "partTitle": "part 2",
					  "typePart" : 2,
					  "statusPart" : 1}]
 

},
{
 "title" : "document 2",
 "status" 2,
 "documentParts" : [{ "partTitle": "part 2",
					  "typePart" : 2,
					  "statusPart" : 2}]
 

},
{
 "title" : "document 3",
 "status" 2,
 "documentParts" : [{ "partTitle": "part 1",
					  "typePart" : 1,
					  "statusPart" : 1},
					{ "partTitle": "part 2",
					  "typePart" : 2,
					  "statusPart" : 2}]
 

},
{
 "title" : "document 4",
 "status" 2,
 "documentParts" : [{ "partTitle": "part 1",
					  "typePart" : 1,
					  "statusPart" : 1},
					{ "partTitle": "part 2",
					  "typePart" : 2,
					  "statusPart" : 1}]

}

I want the documents that have the "documentParts" with "typePart = 1" and "statusPart = 1" In te same item of "documentParts" array.
The wished documents are "document 3" and "document 4" but when I make the following I obtain the "document 1", "document 3", "document 4" because the query search in any element of array, not in the same array item.

Query:

{
  "from": 0,
  "size": 10,  
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "status": {
              "value": 2
            }
          }
        },
        {
          "terms": {
            "documentParts.typePart": [ 1 ],
            "_name": "documentPartFilter"
          }
        },
        {
          "terms": {
            "documentParts.statusPart": [ 1 ],
            "_name": "documentPartStatusFilter"
          }
        }
      ]
    }
  }
}

What is the way to search in the same array item?

Thanks for the help, if you need any clarification, tell me.

[dadoonet (David Pilato)]

Have a look at https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html

[Trino]

Thanks.