How to formulate an and condition for a nested object in Opensearch?

Question

Opensearch ingests documents similar to this example (its just a minimal example):

PUT nested_test/_doc/4
{
    "log": "This is a fourth log message",
    "function": "4 test function",
    "related_objects": [
    { "type": "user", "id": "10" },
    { "type": "offer", "id": "120" }
    ]
}    
PUT nested_test/_doc/5
{
    "log": "This is a fifth log message",
    "function": "5 test function",
    "related_objects": [
    { "type": "user", "id": "120" },
    { "type": "offer", "id": "90" }
    ]
}

With many of these documents, I'd like to filter those which have a specific related object (e.g. type=user and id=120). With the example data above, this should only return the document with id 5. Using simple filters (DQL syntax) as follows does not work:

related_objects.type:user and related_objects.id:120

As this would also match a document 5, as there is a related_object with type user and a related object with id 120, although its not the related user object with id 120, its the related offer.

Look into [nested fields](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html) in elasticsearch. — Paulo, Jun 29 '22 at 15:54

score 1 · Answer 1 · answered Jun 30 '22 at 05:56

Elasticsearch query example:

{
    "query" : {
        "nested" : {
              "path" : "related_objects",
              "query" : {
                   "bool" : {
                      "must" : [
                          {
                             "term" : {"related_objects.type" : "MYTYPE"}
                          },
                          {
                             "term" : {"related_objects.id" : "MYID"}
                          }
                      ]
                   }
              }
        }
    }
}

Basically just go into a nested query and specify all your AND conditions as MUST clauses inside a bool query.

Thanks for your answer. Is it possible to query the required information using DQL or Lucene Syntax? — Daniel, Jun 30 '22 at 08:37
I found that you can simply do `related_objects:{type:"user" and id:120}` in DQL as soon as the field is defined as nested. — Daniel, Jun 30 '22 at 08:59

score 0 · Answer 2 · answered Jun 30 '22 at 03:36

0

If Array[object] is used, the field type is nested, The document reference

answered Jun 30 '22 at 03:36

CangSheng Shen

51
2

score 0 · Accepted Answer · answered Jun 30 '22 at 09:00

As soon as the field is declared as nested field, it is possible to run a simple DQL query to get the desired information:

related_objects:{type:"user" and id:120}

This requires that the field has been defined as nested before:

PUT my-index-000001
{
  "mappings": {
    "properties": {
      "related_objects": {
        "type": "nested" 
      }
    }
  }
}

How to formulate an and condition for a nested object in Opensearch?

3 Answers3