Search query in nested fields with must "AND" "OR" condition


(Myzeest) #1

Hi Guys, I’m using Elasticsearch 6.3. I have created an index with nested fields. My structure is looks like as under:

unit_id “Integer”
unit_name “Text”
availability [Nested]
cal_date “Date”
is_block “Integer”
unit_status “Integer”

My requirement is:

  1. is_block = 0 
    
  2. cal_date must be between “2018-09-01” AND “2018-09-07”
  3. unit_status = 4 (default for all dates)
    
  4. if cal_date = “2018-09-07” then unit_status could be = “4” OR “2”

I’m trying a nested search query it fulfills my above mentioned first 3 requirements, but I have no idea how to implement criteria 4 for a specific date i.e "2018-09-07" than unit_status can be = 4 OR 2.

My index and data is as under:

# Index
PUT rent
{
   "settings": {
	  "number_of_replicas": "0",
	  "number_of_shards": "1"
   },
   "mappings": {
	  "units": {
		 "properties": {
		  "unit_id": { 
		    "type": "integer"
		  }, 
			"unit_name": {
			   "type": "text"
			},
			"availability": {
			   "type": "nested",
			   "properties": {
				  "cal_date": {
					 "type": "date",
					  "format": "YYYY-MM-DD"
				  },
				  "is_block": {
				    "type": "integer"
				  },
				  "unit_status":{
				    "type": "integer"
				  }
			   }
			}
		 }
	  }
   }
}

# Data
POST rent/units/1
{
  "unit_id": 1,
  "unit_name": "unit_001",
  "availability": [
    { "cal_date": "2018-09-01",
      "unit_status": 4,
      "is_block": 0
    },
     { "cal_date": "2018-09-02",
      "unit_status": 4,
      "is_block": 0
    },
     { "cal_date": "2018-09-03",
      "unit_status": 4,
      "is_block": 0
    },
     { "cal_date": "2018-09-04",
      "unit_status": 3,
      "is_block": 1
    },
     { "cal_date": "2018-09-05",
      "unit_status": 4,
      "is_block": 0
    },
    { "cal_date": "2018-09-06",
      "unit_status": 4,
      "is_block": 0
    },
    { "cal_date": "2018-09-07",
      "unit_status": 2,
      "is_block": 0
    },
    { "cal_date": "2018-09-08",
      "unit_status": 3,
      "is_block": 1
    }
    ]
}

 
# My Search Query fulfill above mentioned 3 requirements, but how to implement 4th condition.

GET rent/units/_search
{
   "query": {
	  "nested": {
		 "path": "availability",
		 "score_mode": "avg",
		 "query": {
		   "bool": { "must": [
		     {"match": {"availability.is_block": "0"}},
		     {"match": {"availability.unit_status": "4"}},
		     {
		       "range": {
		         "availability.cal_date": {
		           "gte": "2018-09-01",
		           "lte": "2018-09-07"
		         }
		       } 
		     }
		   ]}},
		 
      "inner_hits": {"size": 10}
	  }
   }
}

(system) #2

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