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

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": "*"
        }
      }
    }
  }
}

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.

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:

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.

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

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