Querying Nested Datatype

Elastic Stack Elasticsearch
1

If I have following index mapping for nested type, can I filter on two different fields in the same document?

Mapping:

{
  "mappings": {
    "records": {
      "properties": {
        "record": {
          "type": "nested",
          "properties": {
            "IntFieldName":    { "type": "string"  },
            "IntFieldValue": { "type": "integer"  },
            "BoolFieldName":     { "type": "string"   },
            "BoolFieldValue":   { "type": "boolean"   },
            "KeywordFieldName":     { "type": "string"   },
            "KeywordFieldValue":   { "type": "keyword"   }
          }
        }
      }
    }
  }
}

Index:

  {
    "record": [
    	{
	    	"IntFieldName": "myint1",
	    	"IntFieldValue": 1,
	    	"BoolFieldName": "mybool1",
	    	"BoolFieldValue": true,
	    	"KeywordFieldName": "mykeyword1",
	    	"KeywordFieldValue": "foo"
    	},
    	{
	    	"IntFieldName": "myint2",
	    	"IntFieldValue": 2,
	    	"BoolFieldName": "mybool2",
	    	"BoolFieldValue": false,
	    	"KeywordFieldName": "mykeyword2",
	    	"KeywordFieldValue": "bar"
    	}
    ]
}

I want to run query equal to something like this:

WHERE IntFieldName = "myint1" 
AND IntFieldValue = 1 
AND IntFieldName = "myInt2" 
AND IntFieldValue = 2

Following is the query, but it does not fetch any results. Is my query incorrect or is it not possible in ES?

Query:

{
  "query": {
    "nested": {
      "path": "record",
      "query": {
        "bool": {
          "filter": [
            { "match": { "record.IntFieldName": "myint1" }},
            { "match": { "record.IntFieldValue":  1 }},
            { "match": { "record.IntFieldName": "myint2" }},
            { "match": { "record.IntFieldValue":  2 }}            
          ]
        }
      }
    }
  }
}
2

A filter acts like a "AND" clause. In the context of nested structures, the query you provided will not return any results because an element inside the array can either match "myint1" or "myint2", not both.

So this will work:

{
	"query": {
		"nested": {
			"path": "record",
			"query": {
				"bool": {
					"filter": [{
							"match": {
								"record.IntFieldName": "myint1"
							}
						},
						{
							"match": {
								"record.IntFieldValue": 1
							}
						}
					]
				}
			},
			"inner_hits": {}
		}
	}
}

I added inner_hits which is a useful clause to see which elements in the array matched.

If you need to return arrays that contain both "myint1" and "myint2", I don't think it is supported directly but I would try the following (which works like an OR) and then check the inner hits object to see if it contains n records (where n = number of terms you are matching, in this case 2).

{
	"query": {
		"nested": {
			"path": "record",
			"query": {
				"bool": {
					"filter": [{
							"terms": {
								"record.IntFieldName": [
									"myint1", "myint2"
								]
							}
						},
						{
							"terms": {
								"record.IntFieldValue": [
									1, 2
								]
							}
						}
					]
				}
			},
			"inner_hits": {}
		}
	}
}
3

@Arvind_Rao Thank you so much for looking into and and adding examples.

If this is not supported directly, I was wondering if there is an alternative schema that you can suggest to achieve the following.

  • An index has two fields for each data type. The intent is to limit the number of fields in an index. For example, for integer type, first field is the name of the field and 2nd field is the value of the field like described in my original example (i.e. IntFieldName and IntFieldValue)
  • There could be multiple fields for each type
  • Support filtering and aggregations. For example, filter with multiple AND or must clauses like explained in the original example.
4

I've the same issue, if you can get a solution i beg you to public in this thread or in mine

Thanks in advance.

5

I guess you learn something new every day :slightly_smiling_face:

This StackOverflow post has the solution to your original question.

{
	"query": {
		"bool": {
			"must": [{
					"nested": {
						"path": "record",
						"query": {
							"bool": {
								"must": [{
										"term": {
											"record.IntFieldName": "myint1"
										}

									},
									{
										"term": {
											"record.IntFieldValue": 1
										}
									}
								]
							}
						}
					}
				},
				{
					"nested": {
						"path": "record",
						"query": {
							"bool": {
								"must": [{
										"term": {
											"record.IntFieldName": "myint2"
										}

									},
									{
										"term": {
											"record.IntFieldValue": 2
										}
									}
								]
							}
						}
					}
				}
			]
		}
	}
}
1 Like
6

That's very true! Thanks for the support!

7

True. We ended up finding the same solution. Thanks so much @Arvind_Rao

8

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