How to search in filtered list of nested objects only

Question

this seems to be a complex one - not sure if it's possible to manage without scripting + I would like to be able to boost name or value fields.

Let's imagine the following documents:

{
  "name":"Red Big Blue document",
  "nested_key_value_properties":[
    {
      "key":"description 1",
      "value":"red"
    },
    {
      "key":"description 2",
      "value":"big"
    },
    {
      "key":"description 3",
      "value":"blue"
    }
  ]
}

{
  "name":"Black Small Red document",
  "nested_key_value_properties":[
    {
      "key":"description 1",
      "value":"red"
    },
    {
      "key":"description 2",
      "value":"small"
    },
    {
      "key":"description 3",
      "value":"black"
    }
  ]
}

{
  "name":"Yellow Big Red document",
  "nested_key_value_properties":[
    {
      "key":"description 1",
      "value":"yellow"
    },
    {
      "key":"description 2",
      "value":"big"
    },
    {
      "key":"description 3",
      "value":"red"
    }
  ]
}

I wish to get the documents that have the key description 1 of the value of red only (first and second document) - the last document should not be in results.

Paulo · Accepted Answer · 2022-02-22 08:51:00Z

2

TLDR;

Elastic flatten objects. Such that

{
  "group" : "fans",
  "user" : [ 
    {
      "first" : "John",
      "last" :  "Smith"
    },
    {
      "first" : "Alice",
      "last" :  "White"
    }
  ]
}

Turn into:

{
  "group" :        "fans",
  "user.first" : [ "alice", "john" ],
  "user.last" :  [ "smith", "white" ]
}

To avoid that you need to set the mapping manually by telling that nested_key_value_properties is going to be a nested field. And then perform a nested query.

See below for how to do so.

To reproduce

PUT /71217348/
{
  "settings": {},
  "mappings": {
    "properties": {
      "name": {
        "type": "text"
      },
      "nested_key_value_properties": {
        "type": "nested",
        "properties": {
          "key": {
            "type": "keyword"
          },
          "value": {
            "type": "keyword"
          }
        }
      }
    }
  }
}

POST /_bulk
{"index":{"_index":"71217348"}}
{"name":"Red Big Blue document","nested_key_value_properties":[{"key":"description 1","value":"red"},{"key":"description 2","value":"big"},{"key":"description 3","value":"blue"}]}
{"index":{"_index":"71217348"}}
{"name":"Black Small Red document","nested_key_value_properties":[{"key":"description 1","value":"red"},{"key":"description 2","value":"small"},{"key":"description 3","value":"black"}]}
{"index":{"_index":"71217348"}}
{"name":"Yellow Big Red document","nested_key_value_properties":[{"key":"description 1","value":"yellow"},{"key":"description 2","value":"big"},{"key":"description 3","value":"red"}]}

GET /71217348/_search
{
  "query": {
    "nested": {
      "path": "nested_key_value_properties",
      "query": {
        "bool": {
          "must": [
            {
              "match": {
                "nested_key_value_properties.key": "description 1"
              }
            },
            {
              "match": {
                "nested_key_value_properties.value": "red"
              }
            }
          ]
        }
      }
    }
  }
}

answered Feb 22, 2022 at 8:51

Paulo

11k5 gold badges23 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user18154574 Over a year ago

Nope, this what I had already and doesn't work - still 3 documents are returned (expectation is that only two will be in the results) but thanks for trying to help :)

Paulo Over a year ago

Well.. I am surprised... When I run it.. I do have only 2 doc being returned.

Paulo Over a year ago

Which version are you running ?

Ankur rana Over a year ago

It is correct @Paulo

user18154574 Over a year ago

@Paulo "number" : "7.16.3", but I ran it once again and it is working now :)

|

Collectives™ on Stack Overflow

How to search in filtered list of nested objects only

1 Answer 1

TLDR;

To reproduce

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

TLDR;

To reproduce

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related