Using query_string to search on wildcard field name and specific value

Elastic Stack Elasticsearch
1

Using query_string to search on wildcard field name and specific value

I'm using NEST to index a doc, which in .net looks like:

public class Supplier
{
    public string AccountId {get; set}
    public int Level {get; set}

    public Dictionary<string, object> Dimensions {get; set;}
}

When it's in the index, it looks something like this:

{
  "accountId": "1d36a070-4699-e611-80r9-005056953547",
  "dimensions": {

    "name": "Some Special Account Name",
	"level": 2

    "workCategory[0].name": "Name 1",
    "workCategory[0].refId": "d9b5ec75-5697-e611-8113-005056956082",
    "workCategory[1].name": "Name 2",
    "workCategory[1].refId": "51b2e075-5692-e611-8113-005055956082",
  }
}

This is a simplistic version of our doc, but for the purposes of the demo, we have to store each workCategory as a flat list like this.

What I'm trying to do is find docs where any 'workCategory refId' is equal to d9b5ec75-5697-e611-8113-005056956082

So I've tried query_string like this:

{
  "query": {
    "query_string": {
        "query": "workCategory[./*].refId:\"d9b5ec75-5697-e611-8113-005056956082\""
	}
  }
}

When I run this, I get hits for all docs - including docs that don't have d9b5ec75-5697-e611-8113-005056956082 in any workCategory[*].refId

I'm not doing anything with the mapping, other than using Automap.

The mapping currently looks like the below
Could it be that the dimensions are not nested? Not sure where I'm going wrong at the moment

{
    "my_index_name": {
        "mappings": {
            "properties": {
                "accountId": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "dimensions": {
                    "properties": {

                        "name": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "level": {
                            "type": "long"
                        },
                        
                        "workCategory[0]": {
                            "properties": {
                                "name": {
                                    "type": "text",
                                    "fields": {
                                        "keyword": {
                                            "type": "keyword",
                                            "ignore_above": 256
                                        }
                                    }
                                },
                                "refId": {
                                    "type": "text",
                                    "fields": {
                                        "keyword": {
                                            "type": "keyword",
                                            "ignore_above": 256
                                        }
                                    }
                                }
                            }
                        },
                        "workCategory[1]": {
                            "properties": {
                                "name": {
                                    "type": "text",
                                    "fields": {
                                        "keyword": {
                                            "type": "keyword",
                                            "ignore_above": 256
                                        }
                                    }
                                },
                                "refId": {
                                    "type": "text",
                                    "fields": {
                                        "keyword": {
                                            "type": "keyword",
                                            "ignore_above": 256
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}
2

The following will work

var defaultIndex = "default_index";
var pool = new SingleNodeConnectionPool(new Uri("http://localhost:9200"));
var settings = new ConnectionSettings(pool)
    .DefaultIndex(defaultIndex);

var client = new ElasticClient(settings);

if (client.Indices.Exists(defaultIndex).Exists)
	client.Indices.Delete(defaultIndex);

var indexResponse = client.LowLevel.Index<StringResponse>(defaultIndex, "1", @"{
  ""accountId"": ""1d36a070-4699-e611-80r9-005056953547"",
  ""dimensions"": {
    ""name"": ""Some Special Account Name"",
    ""level"": 2,
    ""workCategory[0].name"": ""Name 1"",
    ""workCategory[0].refId"": ""d9b5ec75-5697-e611-8113-005056956082"",
    ""workCategory[1].name"": ""Name 2"",
    ""workCategory[1].refId"": ""51b2e075-5692-e611-8113-005055956082""
  }
}", new IndexRequestParameters { Refresh = Refresh.WaitFor });

var searchResponse = client.Search<object>(s => s
	.Query(q => q
		.QueryString(qs => qs
			.Query("workCategory\\[/[0-9]*/\\].refId.keyword:d9b5ec75-5697-e611-8113-005056956082")
		)
	)
);

The [ and ] need to be escaped because they are part of the query syntax. A regular expression, [0-9]* , is embedded, to represent the index.

I would consider rethinking your indexing approach, for a couple of very good reasons:

  1. It makes querying data much harder as you are finding, because indexes are codified into field names.

  2. It can result in a field mapping explosion because each unique field name is a new mapping in the index.

There are other ways in which the index of items in a collection could be stored, if this information is important. For example, flattening out values to objects with properties Key, Value and Index, and mapping a nested datatype. This would allow you to write bool queries that target e.g. Name:workCategory.refId and Value:d9b5ec75-5697-e611-8113-005056956082 and Index:0.

3

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