Query with id, nested array and range in Elastic Search (Open Search AWS)

user_19441528 0
Viewed 20

I have a ES document like below :

    {
        "_id" : "test@domain.com",
        "age" : 12,
        "hobbiles" : ["Singing", "Dancing"]
    
    },
{
        "_id" : "test1@domain.com",
        "age" : 7,
        "hobbiles" : ["Coding", "Chess"]
    
    }

I am storing email as id, age and hobbiles, hobbies is nested type, age is long I want to query with id, age and hobbiles, something like below :

Select * FROM tbl where _id IN ('val1', 'val2') AND age > 5 AND hobbiles should match with Chess or Dancing

How can I do in Elastic Search ? I am using OpenSearch 1.3 (latest) : AWS

elasticsearch nosql amazon-web-services opensearch
1 Answers

I will suspect that field hobbiles is keyword, then the query suggested:

PUT test
{
  "mappings": {
    "properties": {
      "age": {
        "type": "long"
      },
      "hobbiles": {
        "type": "keyword"
      }
    }
  }
}

POST test/_doc/test@domain.com
{
  "age": 12,
  "hobbiles": [
    "Singing",
    "Dancing"
  ]
}
    
POST test/_doc/test1@domain.com
{
  "age": 7,
  "hobbiles": [
    "Coding",
    "Chess"
  ]
}
  
GET test/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "terms": {
            "_id": [
              "test1@domain.com",
              "test@domain.com"
            ]
          }
        }
      ],
      "must": [
        {
          "range": {
            "age": {
              "gt": 5
            }
          }
        },
        {
          "terms": {
            "hobbiles": [
              "Coding",
              "Chess"
            ]
          }
        }
      ]
    }
  }
}
user_187781810
Related