How do I search for a specific value in a nested array in Elastic Search Query?

Question

I'm searching for the following index:

{
    "_index": "example-index",
    "_type": "_doc",
    "_id": "C-123",
    "_version": 1685532751615,
    "_seq_no": 123,
    "_primary_term": 16,
    "found": true,
    "_source": {
        "definitionKey": "example.definition",
        "definitionName": "example",
        "startTime": "2023-05-31T11:32:31.526Z",
        "id": "C-123",
        "state": "active",
        "variables": [
            {
                "id": "VAR-d4a01b91-ffa6-11ed-83be-560acee90493",
                "name": "skills",
                "jsonValue": [
                    "Skill 1"
                ]
            }
        ],
        "definitionId": "C-456",
        "tenantId": ""
    }
}

I'm looking for this index by checking wether:

1. "definitionKey" equals "example.definition"
2. an entry of the "variables" array has a field "name" which matches exactly "skills"
3. the entry from 2. has an "jsonValue" array containing at least one entry which matches exactly "Skill 1"

The first two work flawlessly but 3. doesn't seem to work correctly. I don't get an exception but the index doesn't show up in the result either.

Any idea how to check wether "jsonValue" contains an entry with the value "Skill 1"?

The query I tried:

{
    "query": {
        "bool": {
            "must": [
                {
                    "term": {
                        "definitionKey": "example.definition"
                    }
                },
                {
                    "nested": {
                        "path": "variables",
                        "query": {
                            "bool": {
                                "must": [
                                    {
                                        "term": {
                                            "variables.name": "skills"
                                        }
                                    },
                                    {
                                        "nested": {
                                            "path": "variables",
                                            "query": {
                                                "terms": {
                                                    "variables.jsonValue.keyword": [
                                                        "Skill 1"
                                                    ]
                                                }
                                            }
                                        }
                                    }
                                ]
                            }
                        }
                    }
                }
            ]
        }
    }
}

Answer 1

Analyzing the indexed doc, you only have the variables field with nested type and the jsonValue field is not nested.

That way you don't need to align nested queries. See the example below.

Mapping

PUT idx_test
{
  "mappings": {
    "properties": {
      "variables": {
        "type": "nested",
        "properties": {
          "jsonValue": {
            "type": "keyword"
          }
        }
      }
    }
  }
}

Data

POST idx_test/_doc
    {
      "definitionKey": "example.definition",
      "definitionName": "example",
      "startTime": "2023-05-31T11:32:31.526Z",
      "id": "C-123",
      "state": "active",
      "variables": [
        {
          "id": "VAR-d4a01b91-ffa6-11ed-83be-560acee90493",
          "name": "skills",
          "jsonValue": [
            "Skill 1"
          ]
        }
      ],
      "definitionId": "C-456",
      "tenantId": ""
    }

Query

GET idx_test/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "definitionKey": "example.definition"
          }
        },
        {
          "nested": {
            "path": "variables",
            "query": {
              "bool": {
                "must": [
                  {
                    "term": {
                      "variables.name": "skills"
                    }
                  },
                  {
                    "term": {
                        "variables.jsonValue": {
                        "value": "Skill 1"
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}