Elastic Search query that matches all elements in nested document

[Zedson.Sagar]

I have an index with nested document structure as below -

{
      "firstindex": {
        "mappings": {
          "properties": {
            "Name": {
              "type": "text"
            },
            "SecurityCodes": {
              "type": "nested",
              "properties": {
                "Description": {
                  "type": "text"
                },
                "Name": {
                  "type": "text"
                }
              }
            }
          }
        }
      }
    }

Here is how the docs look like -

{
      "Name": "Doc1",
      "SecurityCodes": [
        {
          "Name": "Restricted",
          "Description": ""
        },
        {
          "Name": "Unclassfied",
          "Description": ""
        }
      ]
    },
    {
      "Name": "Doc2",
      "SecurityCodes": [
        {
          "Name": "Restricted",
          "Description": ""
        },
        {
          "Name": "Restricted",
          "Description": ""
        }
      ]
    },
    {
      "Name": "Doc3",
      "SecurityCodes": [
        {
          "Name": "Restricted",
          "Description": ""
        },
        {
          "Name": "Unknown",
          "Description": ""
        }
      ]
    }

all i want to do is to write a query that returns documents where each securitycode matches the condition.

for e.g. if the search string is Restricted (over secuirtycode.name) it should return doc2 but not doc1 and doc3 since each and every securitycode name matches Restricted under doc2

i tried this but it returns all the docs

    "query": {
    "nested": {
      "path": "SecurityCodes",
      "query": {
        "bool": {
          "must": [
            {
              "term": {
                "SecurityCodes.Name": "Restricted"
              }
            }
          ]
        }
      }
    }
  }

i looked into the answer here but that is not acceptable since it requires a field to exist on the index and we have lot of such nested docs in our index and having a copy feild for each one is not acceptable.

can any one help me out please?

[RabBit_BR (andre.coelho)]

Hi @Zedson.Sagar .

From what I understand you just want the doc where all the SecurityCodes is "SecurityCodes.Name": "Restricted". The query below only searches for "Restricted" and excludes who is "Unclassfied" and "Unknown".

{
  "query": {
    "bool": {
      "must_not": [
        {
          "nested": {
            "path": "SecurityCodes",
            "query": {
              "match": {
                "SecurityCodes.Name": {
                  "query": "Unclassfied"
                }
              }
            }
          }
        },
        {
          "nested": {
            "path": "SecurityCodes",
            "query": {
              "match": {
                "SecurityCodes.Name": {
                  "query": "Unknown"
                }
              }
            }
          }
        }
      ],
      "must": [
        {
          "nested": {
            "path": "SecurityCodes",
            "query": {
              "match": {
                "SecurityCodes.Name": {
                  "query": "Restricted"
                }
              }
            }
          }
        }
      ]
    }
  }
}

[Zedson.Sagar]

Hi @RabBit_BR,

Thanks for your reply, the solution you provided works if the values for security codes are fixed Unclassified, Restricted, Unknown.

However, that is not the case, these values are dynamic and keep expanding.

Is there a way i can use a double negation to extract only objects that doesn't have fields that are different to the given one?

this is how we can handle in SQL database -

Parent docs are in OBJECTS table

SecurityCodes are in SECURITYCODES Table

a PARENTSECURITYCODE table that relates both Parent and Security

(PARENT can have any number of Security Codes)

Select * from OBJECTS O1 
Where NOT EXISTS ( SELECT * FROM PARENTSECURITYCODE PSC INNER JOIN SECURITYCODE SC ON SC.NAME = PSC.CODENAME WHERE O1.NAME = PSC.PARENTNAME AND NOT SC.NAME = 'Restricted')

We would want to address this scenario in Elastic as well. Is ther a way i can do this?

[Mikhail_Khludnev]

{
  "query": {
    "bool": {
      "must_not": [               // exclude those parents, which ... 
        {
          "nested": {
            "path": "SecurityCodes",
            "query": {
                "bool": {
                     "must": [            // .. has any child ...
                          "exists": {
                                "field": "SecurityCodes.Name"
                            }
                     ],
                     "must_not": [      // ... but not Restricted
                           { 
                               "match": {
                                     "SecurityCodes.Name": {
                                     "query": "Restricted"
                                  }
                               }
                           }
                     ],
                }
           }
       ]
    }
  }
}

Assuming that ES handles top pure negation query, it might do the thing.

[Zedson.Sagar]

Thanks @Mikhail_Khludnev this works. Appreciate your help