I am getting unexpected result from Elasticsearch while searching though object property which are inside nested property. I am using elasticsearch-dsl python library for creating document and querying. Is this bug or I am missing something in querying and mapping parts? Below are the elasticsearch json mapping, query, unexpected result and expected result
Mapping:
{
"deal_acls": {
"type": "nested",
"properties": {
"created_at": {
"type": "date"
},
"created_by": {
"properties": {
"id": {
"type": "keyword"
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"status": {
"type": "keyword",
"normalizer": "lowercase"
}
}
},
"permission": {
"properties": {
"CRM": {
"properties": {
"description": {
"properties": {
"created_by": {
"type": "keyword",
"normalizer": "lowercase"
},
"object": {
"type": "keyword",
"normalizer": "lowercase"
},
"object_id": {
"type": "keyword",
"normalizer": "lowercase"
},
"timestamp": {
"type": "date"
}
}
},
"permission": {
"properties": {
"delete": {
"type": "integer"
},
"edit": {
"type": "integer"
},
"manage": {
"type": "long"
},
"read": {
"type": "integer"
},
"write": {
"type": "integer"
}
}
}
}
},
"deal": {
"properties": {
"description": {
"properties": {
"created_by": {
"type": "keyword",
"normalizer": "lowercase"
},
"object": {
"type": "keyword",
"normalizer": "lowercase"
},
"object_id": {
"type": "keyword",
"normalizer": "lowercase"
},
"timestamp": {
"type": "date"
}
}
},
"permission": {
"properties": {
"delete": {
"type": "integer"
},
"edit": {
"type": "integer"
},
"manage": {
"type": "long"
},
"read": {
"type": "integer"
},
"write": {
"type": "integer"
}
}
}
}
},
"document": {
"properties": {
"description": {
"properties": {
"created_by": {
"type": "keyword",
"normalizer": "lowercase"
},
"object": {
"type": "keyword",
"normalizer": "lowercase"
},
"object_id": {
"type": "keyword",
"normalizer": "lowercase"
},
"timestamp": {
"type": "date"
}
}
},
"permission": {
"properties": {
"delete": {
"type": "integer"
},
"edit": {
"type": "integer"
},
"manage": {
"type": "long"
},
"read": {
"type": "integer"
},
"write": {
"type": "integer"
}
}
}
}
},
"external_deal_team": {
"properties": {
"description": {
"properties": {
"created_by": {
"type": "keyword",
"normalizer": "lowercase"
},
"object": {
"type": "keyword",
"normalizer": "lowercase"
},
"object_id": {
"type": "keyword",
"normalizer": "lowercase"
},
"timestamp": {
"type": "date"
}
}
},
"permission": {
"properties": {
"delete": {
"type": "integer"
},
"edit": {
"type": "integer"
},
"manage": {
"type": "long"
},
"read": {
"type": "integer"
},
"write": {
"type": "integer"
}
}
}
}
},
"internal_deal_team": {
"properties": {
"description": {
"properties": {
"created_by": {
"type": "keyword",
"normalizer": "lowercase"
},
"object": {
"type": "keyword",
"normalizer": "lowercase"
},
"object_id": {
"type": "keyword",
"normalizer": "lowercase"
},
"timestamp": {
"type": "date"
}
}
},
"permission": {
"properties": {
"delete": {
"type": "integer"
},
"edit": {
"type": "integer"
},
"manage": {
"type": "long"
},
"read": {
"type": "integer"
},
"write": {
"type": "integer"
}
}
}
}
}
}
},
"status": {
"type": "keyword",
"normalizer": "lowercase"
},
"updated_at": {
"type": "date"
},
"updated_by": {
"properties": {
"id": {
"type": "keyword"
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"status": {
"type": "keyword",
"normalizer": "lowercase"
}
}
},
"user": {
"properties": {
"id": {
"type": "keyword"
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"status": {
"type": "keyword",
"normalizer": "lowercase"
}
}
}
}
}
}
Query:
{
"query": {
"bool": {
"must": [
{
"nested": {
"path": "deal_acls",
"query": {
"term": {
"deal_acls.permission.deal.permission.read": 1
}
}
}
},
{
"nested": {
"path": "deal_acls",
"query": {
"terms": {
"deal_acls.user.id": [
"5f7cea05-6562-4bdd-8448-19cfbe11783a"
]
}
}
}
}
]
}
}
}
Unexpected result: Since the deal permission of user with id=5f7cea05-6562-4bdd-8448-19cfbe11783a is 0, it should be returning empty hits.
{
"took": 2,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 1,
"relation": "eq"
},
"max_score": 2,
"hits": [
{
"_index": "dev.crecentric.dealvault.deals",
"_type": "_doc",
"_id": "a928838d-3422-41db-b40e-28f5c793f806",
"_score": 2,
"_source": {
"id": "a928838d-3422-41db-b40e-28f5c793f806",
"deal_acls": [
{
"user": {
"id": "5f7cea05-6562-4bdd-8448-19cfbe11783a",
"name": "testerrrrs testesssss",
"status": "active"
},
"permission": {
"deal": {
"permission": {
"edit": 0,
"read": 0,
"write": 0,
"delete": 0,
"manage": 0
},
"description": {
"object": "workspace",
"object_id": "fbc840b1-8727-4945-a070-fa1c105f9550",
"timestamp": "2022-02-10T05:46:05.140867 00:00",
"created_by": "d78411e5-6645-4b95-a98c-db6db8748580"
}
},
"external_deal_team": {
"permission": {
"edit": 0,
"read": 1,
"write": 1,
"delete": 0,
"manage": 0
},
"description": {
"object": "workspace",
"object_id": "fbc840b1-8727-4945-a070-fa1c105f9550",
"timestamp": "2022-02-10T05:46:05.140902 00:00",
"created_by": "d78411e5-6645-4b95-a98c-db6db8748580"
}
},
"internal_deal_team": {
"permission": {
"edit": 0,
"read": 1,
"write": 1,
"delete": 0,
"manage": 0
},
"description": {
"object": "workspace",
"object_id": "fbc840b1-8727-4945-a070-fa1c105f9550",
"timestamp": "2022-02-10T05:46:05.140910 00:00",
"created_by": "d78411e5-6645-4b95-a98c-db6db8748580"
}
}
},
"status": "active",
"created_at": "2022-02-10T05:40:15.727598 05:45",
"updated_at": "2022-02-10T05:46:05.177076 05:45"
},
{
"user": {
"id": "d78411e5-6645-4b95-a98c-db6db8748580",
"name": "Ramesh Pradhan",
"status": "active"
},
"permission": {
"CRM": {
"permission": {
"edit": 1,
"read": 1,
"write": 1,
"delete": 1,
"manage": 1
},
"description": {
"object": "owner",
"object_id": "d78411e5-6645-4b95-a98c-db6db8748580",
"timestamp": "2022-02-10T05:35:41.453881 00:00",
"created_by": "d78411e5-6645-4b95-a98c-db6db8748580"
}
},
"deal": {
"permission": {
"edit": 1,
"read": 1,
"write": 1,
"delete": 1,
"manage": 1
},
"description": {
"object": "owner",
"object_id": "d78411e5-6645-4b95-a98c-db6db8748580",
"timestamp": "2022-02-10T05:35:41.453881 00:00",
"created_by": "d78411e5-6645-4b95-a98c-db6db8748580"
}
},
"document": {
"permission": {
"edit": 1,
"read": 1,
"write": 1,
"delete": 1,
"manage": 1
},
"description": {
"object": "owner",
"object_id": "d78411e5-6645-4b95-a98c-db6db8748580",
"timestamp": "2022-02-10T05:35:41.453881 00:00",
"created_by": "d78411e5-6645-4b95-a98c-db6db8748580"
}
},
"external_deal_team": {
"permission": {
"edit": 1,
"read": 1,
"write": 1,
"delete": 1,
"manage": 1
},
"description": {
"object": "owner",
"object_id": "d78411e5-6645-4b95-a98c-db6db8748580",
"timestamp": "2022-02-10T05:35:41.453881 00:00",
"created_by": "d78411e5-6645-4b95-a98c-db6db8748580"
}
},
"internal_deal_team": {
"permission": {
"edit": 1,
"read": 1,
"write": 1,
"delete": 1,
"manage": 1
},
"description": {
"object": "owner",
"object_id": "d78411e5-6645-4b95-a98c-db6db8748580",
"timestamp": "2022-02-10T05:35:41.453881 00:00",
"created_by": "d78411e5-6645-4b95-a98c-db6db8748580"
}
}
},
"status": "active",
"created_at": "2022-02-10T05:35:41.453913 05:45",
"updated_at": "2022-02-10T05:35:41.462956 05:45"
}
]
}
}
]
}
}
Expected result:
{
"took": 2,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 1,
"relation": "eq"
},
"max_score": 2,
"hits": []
}
}
CodePudding user response：
You are using two nested queries: so the result is a document which contains "deal_acls.permission.deal.permission.read": 1 and "deal_acls.user.id": ["5f7cea05-6562-4bdd-8448-19cfbe11783a"], but not necessary in the same subobject of the nested field. Try using a terms query inside a single nested query:
{
"query": {
"nested": {
"path": "deal_acls",
"query": {
"bool": {
"must": [
{ "match": { "deal_acls.user.id": "5f7cea05-6562-4bdd-8448-19cfbe11783a" }},
{ "match": { "deal_acls.permission.deal.permission.read": 1 }}
]
}
}
}
}
}
CodePudding user response：
In case if someone is searching for answer, I have fixed this using this query:
{
"query": {
"nested": {
"path": "deal_acls",
"query": {
"bool": {
"must": [
{
"term": {
"deal_acls.permission.deal.permission.read": 1
}
},
{
"terms": {
"deal_acls.user.id": [
"5f7cea05-6562-4bdd-8448-19cfbe11783a"
]
}
}
]
}
}
}
}
}