How to query a nested object with combined AND and OR logic?

Elastic Stack Elasticsearch
1

Hi,

How should this expression be written as a query:

(attributes.id = 14 OR attributes.id = 15) AND (attributes.id = 4843 OR attributes.id = 4859)

The nested object looks like this:

{  
  "attributes":[  
    {  
      "id":14,
      "type":"color",
      "name":"Sort",
      "version":1
    },
    {  
      "id":15,
      "type":"color",
      "name":"Sølv",
      "version":1
    },
    {  
      "id":2031,
      "type":"brand",
      "name":"Jimmy Choo",
      "version":1
    },
    {  
      "id":4843,
      "type":"size",
      "name":"36x28",
      "version":1
    },
    {  
      "id":4859,
      "type":"size",
      "name":"38x36",
      "version":1
    },
    {  
      "id":4927,
      "type":"size",
      "name":"60J",
      "version":1
    },
    {  
      "id":4958,
      "type":"size",
      "name":"75F",
      "version":1
    }
  ]
}

I've tried using this query - among many - without any luck:

{  
  "query":{  
    "nested":{  
      "path":"attributes",
      "query":{  
        "bool":{  
          "should":[  
            {  
              "terms":{  
                "attributes.id":[  
                  14,
                  15
                ]
              }
            },
            {  
              "terms":{  
                "attributes.id":[  
                  4843,
                  4859
                ]
              }
            }
          ],
          "minimum_should_match":2
        }
      }
    }
  }
}

Any help would appreciated.

2

Not sure why this does't match, but I would replace the should key with must key and then remove the minimum_should_match key then it maps more directly to your logical query. Can you run this query in the explain and validate query APIs?

3

Thanks for your suggestion! The validation API says the following query is valid but I'm still not getting any hits:

GET migration2/product/_search
{
  "query": {
    "nested": {
      "path": "variants.attributes",
      "query": {
        "bool": {
          "must": [
            {
              "terms": {
                "variants.attributes.id": [204]
              }
            },
            {
              "terms": {
                "variants.attributes.id": [2627]
              }
            }
          ]
        }
      }
    }
  }
}

Please note I used some other values to match the resultset. The attributes are also a nested object to variants, but that shouldn't make any difference, right?

This is the response from the explain API:

{
  "_index": "migration2",
  "_type": "product",
  "_id": "5d25c131748f7d98c589fcfbb68d451e.204",
  "matched": false,
  "explanation": {
    "value": 0,
    "description": "Failure to meet condition(s) of required/prohibited clause(s)",
    "details": [
      {
        "value": 0,
        "description": "no match on required clause (ToParentBlockJoinQuery (+variants.attributes.id:[204 TO 204] +variants.attributes.id:[2627 TO 2627]))",
        "details": [
          {
            "value": 0,
            "description": "Not a match",
            "details": []
          }
        ]
      },
      {
        "value": 0,
        "description": "match on required clause, product of:",
        "details": [
          {
            "value": 0,
            "description": "# clause",
            "details": []
          },
          {
            "value": 1,
            "description": "_type:product, product of:",
            "details": [
              {
                "value": 1,
                "description": "boost",
                "details": []
              },
              {
                "value": 1,
                "description": "queryNorm",
                "details": []
              }
            ]
          }
        ]
      }
    ]
  }
}

UPDATED:

This is the document trying to match:

{
  "_index" : "migration2",
  "_type" : "product",
  "_id" : "5d25c131748f7d98c589fcfbb68d451e.204",
  "_version" : 1,
  "found" : true,
  "_source" : {
    "status" : "draft",
    "variants" : [
      {
        "version" : 1,
        "sku" : "f4e1cd4583811f4e8e37a8c56eb4616c",
        "gtin" : null,
        "attributes" : [
          {
            "id" : 204,
            "type" : "color",
            "name" : "Lilla",
            "version" : 1
          },
          {
            "id" : 2627,
            "type" : "brand",
            "name" : "Nike",
            "version" : 1
          }
        ],
        "attributes_optimized" : {
          "brands" : [
            2627
          ],
          "colors" : [
            204
          ],
          "sizes" : [ ]
        },
        "attributes_array" : [
          2627,
          204
        ]
      }
    ]
  }
}

Note: there are three different attributes fields because I'm comparing their performances.

4

What do your mappings look like?

5

There is no single nested object in your example document that can match both must clauses of your bool query. You need a bool query with two separate nested queries.

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "variants.attributes",
            "query": {
              "terms": {
                "variants.attributes.id": [
                  204
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "variants.attributes",
            "query": {
              "terms": {
                "variants.attributes.id": [
                  2627
                ]
              }
            }
          }
        }
      ]
    }
  }
}
1 Like
6

Thanks, this solved my problem!

However, I had to wrap the query within another nested query to ensure the single variant to have all attributes (not mentioned in my original post):

{
  "query": {
    "nested": {
      "path": "variants",
      "query": {
        "bool": {
          "must": [
            {
              "nested": {
                "path": "variants.attributes",
                "query": {
                  "terms": {
                    "variants.attributes.id": [
                      204
                    ]
                  }
                }
              }
            },
            {
              "nested": {
                "path": "variants.attributes",
                "query": {
                  "terms": {
                    "variants.attributes.id": [
                      2627
                    ]
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}
7

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