Find any document with a value in a nested path/structure using wild cards

Elastic Search
1

I am trying to write a query that will find any document where the path campaigns.erp*.campaigns.campaignCode contains a value. I am stuck.

Do you guys have any idea how to achieve this?

If you run the code below then I would expect the query to return 2 documents since the first and last POST contains documents where the path campaigns.erp*.campaigns.campaignCode contains a value.

I tried using the query strings API, but I did not make much progress.

PUT /campaigns
{
  "mappings": {
    "properties": {
      "campaigns": {
        "type": "nested",
        "properties": {
          "erp1": {
            "properties": {
              "campaigns": {
                "type": "nested",
                "properties": {
                  "campaignCode": {
                    "type": "keyword"
                  }
                }
              }
            }
          },
          "erp2": {
            "properties": {
              "campaigns": {
                "type": "nested",
                "properties": {
                  "campaignCode": {
                    "type": "keyword"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

POST /campaigns/_doc
{
  "campaigns": {
    "erp1": {
      "campaigns": [
        {
          "campaignCode": "1"
        }
      ]
    },
    "erp2": {
      "campaigns": [
        {
          "campaignCode": "2"
        }
      ]
    }
  }
}

POST /campaigns/_doc
{
  "campaigns": {
    "erp1": {
      "campaigns": []
    },
    "erp2": {
      "campaigns": []
    }
  }
}

POST /campaigns/_doc
{
  "campaigns": {
    "erp1": {
      "campaigns": []
    },
    "erp2": {
      "campaigns": [
        {
          "campaignCode": "3"
        }
      ]
    }
  }
}


GET /campaigns/_search
{
  "query": {
    "nested": {
      "path": "campaigns",
      "query": {
        "query_string": {
          "default_field": "campaigns.erp*.campaigns.campaignCode", 
          "query": "*"
        }
      }
    }
  }
}
2

Hey @mahnac, your issue is that you have two nested fields so you have to use a nested subquery.

Try this:

GET /campaigns/_search
{
  "query": {
    "nested": {
      "path": "campaigns",
      "query": {
        "nested": {
          "path": "campaigns.erp2.campaigns",
          "query": {
            "query_string": {
               "default_field": "campaigns.erp*.campaigns.campaignCode",
               "query": "*"
              
            }
          }
        }
      }
    }
  }
}

We have another good example of multi-level nested queries in our docs.

3

Hello Kathleen,

Thank you for your suggestions and the link to the docs. Your query is what I am almost looking for, however, if I adjust the line "path": "campaigns.erp2.campaigns" to "path": "campaigns.erp1.campaigns" the result is only 1 hit instead of the 2 hits that your query provides. I need the line to be arbitrary, so it does not matter whether its is "campaigns.erp2.campaigns" or "campaigns.erp1.campaigns", but more like "campaigns.erp*.campaigns". Is this possible? :slight_smile:

4

Right, I don't think you can do that, but if you have multiple nested calls like that (there has to be a finite number since you have to maintain the mappings) you could combine them in a boolean query.

5

Okay, I came up with a different solution to the problem, but thank you very much for your assistance and help.