Hi there! I’m currently working on a project that requires me to search for emails on a specific field within the document. I’m trying the following query:
{
"query": {
"bool": {
"must": [
{
"terms": {
"attendant.email": ["attendant@email.com"]
}
}
]
}
},
"size": 10
}
The documents within the index are like this one (I had to anonymize its data due to local laws):
{
"_index": "opportunity",
"_id": "3575511",
"_score": 4.4735565,
"_source": {
"id": 3575511,
"campaign_id": 260,
"campaign_name": "Name",
"campaign_message": "Campaign message",
"campaign_end_date": "0001-01-01T00:00:00Z",
"event_campaign": {
"conversion": false,
"type": false
},
"stage_id": 4,
"stage": {
"id": 4,
"name": "Stage Name",
"description": "Stage Description",
"slug": "stage-slug"
},
"status_id": 6,
"status": {
"id": 6,
"name": "Status Name",
"description": "Status Description",
"slug": "status-slug",
"status_type": 0
},
"origin_id": 0,
"attendant_id": 543,
"title": "Opportunity Title",
"value": 0,
"subtotal": 0,
"comment": "",
"client": {
"id": 2485260,
"registered": true,
"id_fc": 15982726,
"email": "client@email.com",
"name": "Client Name",
"document": "01020300506070809",
"representative": "",
"phones": [
{
"id": 5483488,
"type": true,
"ddi": 55,
"number": "000102030405",
"created_at": "2023-10-04T23:48:42Z",
"updated_at": "2024-09-26T14:23:28Z"
}
],
"created_at": "2023-10-04T23:48:42Z",
"updated_at": "2024-09-26T14:23:28Z"
},
"created_by": "",
"created_at": "2024-09-26T14:23:28Z",
"updated_at": "2024-09-26T14:23:28Z",
"lifetime": "Since 4 months and 4 days",
"tasks_total": 0,
"tasks_open": 0,
"date_next_task": "",
"favorite": false,
"finished": false,
"reason_loss_id": 0,
"reason_loss_name": "",
"sales_opportunity": 0,
"attendant": {
"id": 543,
"email": "attendant@email.com",
"name": "Attendant Name"
},
"order": null,
"order_db": null,
"tasks": null,
"budget": null,
"products": null,
"type_change": null,
"type_opportunity": false,
"conversion_opportunity": false,
"bond_exchange": null,
"coupon": {
"id": 0,
"opportunity_id": 0,
"promo_id": 0,
"code": "",
"active": false,
"updated_at": "0001-01-01T00:00:00Z"
}
}
}
The idea is that there might be multiple emails within the termsclause and all documents whose attendant.email matches any of the given emails return from the search. I’ve tried with a quite big list of emails (which in theory should’ve matched nearly all - if not all - documents within the index), but got no results.
Here is the document mapping:
{
"opportunity": {
"mappings": {
"properties": {
"attendant": {
"properties": {
"email": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"id": {
"type": "long"
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
},
"attendant_id": {
"type": "long"
},
"budget": {
"properties": {
"cart_id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"cbid": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"created_at": {
"type": "date"
},
"id": {
"type": "long"
},
"link_cart": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"opportunity_id": {
"type": "long"
},
"origin_id": {
"type": "long"
},
"recipe_id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"sent_at": {
"type": "date"
},
"updated_at": {
"type": "date"
}
}
},
"campaign_end_date": {
"type": "date"
},
"campaign_id": {
"type": "long"
},
"campaign_message": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"campaign_name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"client": {
"properties": {
"created_at": {
"type": "date"
},
"document": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"email": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"id": {
"type": "long"
},
"id_fc": {
"type": "long"
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"phones": {
"properties": {
"created_at": {
"type": "date"
},
"ddi": {
"type": "long"
},
"id": {
"type": "long"
},
"number": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"type": {
"type": "boolean"
},
"updated_at": {
"type": "date"
}
}
},
"registered": {
"type": "boolean"
},
"representative": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"updated_at": {
"type": "date"
}
}
},
"comment": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"conversion_opportunity": {
"type": "boolean"
},
"coupon": {
"properties": {
"active": {
"type": "boolean"
},
"code": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"id": {
"type": "long"
},
"opportunity_id": {
"type": "long"
},
"promo_id": {
"type": "long"
},
"updated_at": {
"type": "date"
}
}
},
"created_at": {
"type": "date"
},
"created_by": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"date_next_task": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"event_campaign": {
"properties": {
"conversion": {
"type": "boolean"
},
"type": {
"type": "boolean"
}
}
},
"favorite": {
"type": "boolean"
},
"finished": {
"type": "boolean"
},
"id": {
"type": "long"
},
"lifetime": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"order": {
"properties": {
"approval_at": {
"properties": {
"Time": {
"type": "date"
},
"Valid": {
"type": "boolean"
}
}
},
"created_at": {
"type": "date"
},
"creation_at": {
"type": "date"
},
"external_conversion": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"external_id": {
"type": "long"
},
"id": {
"type": "long"
},
"opportunity_id": {
"type": "long"
},
"order_number": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"payment": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"updated_at": {
"type": "date"
}
}
},
"origin_id": {
"type": "long"
},
"products": {
"properties": {
"brand": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"cabecasa": {
"type": "boolean"
},
"created_at": {
"type": "date"
},
"id": {
"type": "long"
},
"image": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"opportunity_id": {
"type": "long"
},
"quantity": {
"type": "long"
},
"seller": {
"properties": {
"id": {
"type": "long"
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
},
"sku": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"total_price": {
"type": "float"
},
"type": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"unit_price": {
"type": "float"
},
"updated_at": {
"type": "date"
}
}
},
"reason_loss_id": {
"type": "long"
},
"reason_loss_name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"sales_opportunity": {
"type": "long"
},
"stage": {
"properties": {
"description": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"id": {
"type": "long"
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"slug": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
},
"stage_id": {
"type": "long"
},
"status": {
"properties": {
"description": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"id": {
"type": "long"
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"slug": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"status_type": {
"type": "long"
}
}
},
"status_id": {
"type": "long"
},
"subtotal": {
"type": "float"
},
"tasks": {
"properties": {
"comment": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"completed": {
"type": "boolean"
},
"created_at": {
"type": "date"
},
"day_of_week": {
"type": "long"
},
"end_date": {
"properties": {
"Time": {
"type": "date"
},
"Valid": {
"type": "boolean"
}
}
},
"execution_date": {
"type": "date"
},
"id": {
"type": "long"
},
"id_fc": {
"type": "long"
},
"op_id": {
"type": "long"
},
"op_stage_Id": {
"type": "long"
},
"op_title": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"op_value": {
"type": "float"
},
"phone": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"removal_date": {
"properties": {
"Time": {
"type": "date"
},
"Valid": {
"type": "boolean"
}
}
},
"removed": {
"type": "boolean"
},
"task_period_id": {
"type": "long"
},
"task_type_id": {
"type": "long"
},
"updated_at": {
"type": "date"
}
}
},
"tasks_open": {
"type": "long"
},
"tasks_total": {
"type": "long"
},
"title": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"type_opportunity": {
"type": "boolean"
},
"updated_at": {
"type": "date"
},
"value": {
"type": "float"
}
}
}
}
}
I’m quite new to OpenSearch and I’m having a hard time on this. What I’m doing wrong? Thanks in advance!