IMPORTANT EDIT: The logic described below appears to be all correct. The root of my issue was actually being caused by the utility we used to push new data to the ES database, not with the query itself. I have accepted the answer that says that the query works as intended.
I have an Elasticsearch server whose mapping looks like this (as output by curl 'elastic:9200/resourcelibrary/_mapping):
{
"resourcelibrary": {
"mappings": {
"resource": {
"properties": {
"created_at": {
"type": "date"
},
"created_by": {
"type": "text"
},
"custom_key": {
"type": "keyword"
},
"defaultAction": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"default_action": {
"type": "keyword"
},
"description": {
"type": "text"
},
"id": {
"type": "text"
},
"indexed": {
"type": "keyword"
},
"is_searchable": {
"type": "keyword"
},
"key": {
"type": "text"
},
"licenses": {
"type": "keyword"
},
"raw": {
"type": "text"
},
"require_priv": {
"type": "keyword"
},
"source": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
},
"fielddata": true
},
"stat": {
"type": "text"
},
"style_def": {
"type": "keyword"
},
"tags": {
"type": "text"
},
"thumbnail": {
"type": "text"
},
"title": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"type": {
"type": "keyword"
},
"uid": {
"type": "text"
},
"updated_at": {
"type": "date"
},
"updated_by": {
"type": "text"
}
}
}
}
}
}
With the resourcelibrary collection completely empty, I add the following documents to it:
[
{
'type' : 'video',
'uid' : '2c444278-e0d3-497b-9b5b-b70756b0fdc0',
'key' : 'test-test',
'custom_key' : 'test-test',
'description' : 'Random text just to fill up the description. Also, math',
'privileged' : [],
'require_priv' : true,
'title' : 'Title!!!',
'defaultAction': '9dfcdb39-6644-4023-82c3-8227ba184c02',
'source' : 'frontline'
},
{
'type' : 'course',
'uid' : '8afb5c95-c7b5-498a-abec-ae829d164964',
'key' : 'test-scorm',
'custom_key' : 'test-scorm',
'description' : 'SCORM!!!',
'privileged' : [],
'require_priv' : true,
'title' : 'SCORM!!!',
'defaultAction': '1302dead-9941-4b90-b35c-30eff4993365',
'source' : 'scormcloud'
},
{
'type' : 'mc',
'uid' : '8e66c2fa-6090-49da-91dd-d939124fef90',
'key' : 'test-mc',
'custom_key' : 'test-mc',
'description' : 'MC!!!',
'require_priv' : false,
'title' : 'MC!!!',
'defaultAction': '7957b8f5-c934-4296-b7bb-70f2cc4b2ad0',
'source' : 'edivate'
},
{
'type' : 'group',
'uid' : '80a908c3-dd6c-4902-9f05-647a8af689ac',
'key' : 'test-group',
'custom_key' : 'test-group',
'description' : 'GROUP!!!',
'require_priv' : false,
'title' : 'GROUP!!!',
'defaultAction': '25b700a5-7563-4d6e-9eab-18465d08a683',
'source' : 'two words'
},
{
'type' : 'video',
'uid' : '3d555389-e0d3-497b-9b5b-c81867c10ed1',
'key' : 'test-video',
'custom_key' : 'test-video',
'description' : 'Random text just to fill up the description. Also, science',
'require_priv' : false,
'title' : 'NO-PRIVS-REQUIRED RESOURCE!!!',
'defaultAction': '9dfcdb39-6644-4023-82c3-8227ba184c02',
'source' : 'ets'
},
{
'type' : 'video',
'uid' : 'fbc0f853-9020-4ed7-8d4d-e18ebe75d815',
'key' : 'test-test-test',
'custom_key' : 'test-test-test',
'description' : 'integration testing description',
'require_priv' : false,
'title' : 'Search All Resources Integration Title',
'defaultAction': '9dfcdb39-6644-4023-82c3-8227ba184c02'
},
{
'type' : 'file',
'uid' : 'cf84e252-1082-4a94-9fe5-45fa73364e2f',
'key' : 'test-test-test two',
'custom_key' : 'test-test-test two',
'description' : 'integration testing description two',
'require_priv' : false,
'title' : 'Search All Resources Integration Title two',
'defaultAction': '0b7abf9e-c88a-4d19-891d-52fe0b220506'
},
{
'id' : 'ba462b70-de73-4173-88bf-66bc9d1385b9',
'type' : 'course',
'uid' : 'scormcloud-course-cf84e252-1082-4a94-9fe5-45fa73364e2f',
'key' : 'test-test-test two',
'custom_key' : 'test-test-test two',
'description' : 'integration testing description two',
'require_priv' : false,
'title' : 'Search All Resources Integration Title two',
'defaultAction': '0b7abf9e-c88a-4d19-891d-52fe0b220506'
},
{
'id' : '7f0cbbc6-a1dd-43ca-9108-b31f90904dce',
'type' : 'course',
'uid' : 'scormcloud-course-7f0cbbc6-a1dd-43ca-9108-b31f90904dce',
'key' : 'LD_7f0cbbc6-a1dd-43ca-9108-b31f90904dce',
'custom_key' : 'LD_7f0cbbc6-a1dd-43ca-9108-b31f90904dce',
'description' : 'This is a LearningDesigner course',
'require_priv' : false, // Still requires LCR Tooling
'title' : 'LearningDesigner Course 1',
'defaultAction': '1302dead-9941-4b90-b35c-30eff4993365'
},
{
'id' : '9e195a62-1a53-42c0-8648-4aa35c309d48',
'type' : 'course',
'uid' : 'scormcloud-course-9e195a62-1a53-42c0-8648-4aa35c309d48',
'key' : 'user-SCORM_9e195a62-1a53-42c0-8648-4aa35c309d48',
'custom_key' : 'user-SCORM_9e195a62-1a53-42c0-8648-4aa35c309d48',
'description' : 'This is a user-uploaded SCORM course',
'require_priv' : false, // Still requires LCR Tooling
'title' : 'User-Uploaded Course 1',
'defaultAction': '1302dead-9941-4b90-b35c-30eff4993365'
},
{
'id' : '2b1b2197-48ae-4669-8bfa-7edd440cb027',
'type' : 'course',
'uid' : 'course-2b1b2197-48ae-4669-8bfa-7edd440cb027',
'source' : 'canvas',
'key' : '9e195a62-1a53-42c0-8648-4aa35c309d48',
'custom_key' : '9e195a62-1a53-42c0-8648-4aa35c309d48',
'description' : 'This is a Canvas course',
'require_priv' : false,
'title' : 'Canvas Course 1',
'defaultAction': '7aef5ef2-e0c1-4188-9e74-3e24057e7e6e'
},
{
'id' : '5e113295-f905-4a3d-97e0-f5d49926c979',
'type' : 'collaborative',
'uid' : 'frontline-collaborative-5e113295-f905-4a3d-97e0-f5d49926c979',
'key' : '5e113295-f905-4a3d-97e0-f5d49926c979',
'custom_key' : '5e113295-f905-4a3d-97e0-f5d49926c979',
'description' : 'This is a Collaborative resource',
'require_priv' : false,
'title' : 'Collab Resource 1',
'defaultAction': 'e153409d-3330-4202-baf2-d602b4cb7d66'
},
]
I am working with BodyBuilder.JS to produce a query that returns everything EXCEPT for documents that meets any of the following criteria:
typeis equal to "collaborative"sourceis equal to "canvas"typeis equal to "course" ANDcustom_keystarts with "LD_"typeis equal to "course" ANDcustom_keystarts with "user-SCORM_"
In my application, I implemented these exclusive conditions like so (note: body is an object created with the bodybuilder() function from BodyBuilder.JS):
body.notFilter('term', 'type', 'collaborative');
body.notFilter('term', 'source', 'canvas');
body.notFilter('bool', subFilter => {
subFilter.filter('term', 'type', 'course');
subFilter.filter('regexp', 'custom_key', 'LD_.*');
return subFilter;
});
body.notFilter('bool', subFilter => {
subFilter.filter('term', 'type', 'course');
subFilter.filter('regexp', 'custom_key', 'user-SCORM_.*');
return subFilter;
});
... And when body.build() is called, it produces the following Elasticsearch DSL string:
{
"from": "0",
"size": 100,
"query": {
"bool": {
"filter": {
"bool": {
"must": {
"bool": {}
},
"must_not": [
{
"term": {
"type": "collaborative"
}
},
{
"term": {
"source": "canvas"
}
},
{
"bool": {
"must": [
{
"term": {
"type": "course"
}
},
{
"regexp": {
"custom_key": "LD_.*"
}
}
]
}
},
{
"bool": {
"must": [
{
"term": {
"type": "course"
}
},
{
"regexp": {
"custom_key": "user-SCORM_.*"
}
}
]
}
}
]
}
}
}
}
}
When I send this query to my Elasticsearch server, it correctly omits the documents with type of "collaborative" and source of "canvas". However, it is doing the following incorrect things:
- search results are missing the second document (
uidvalue "8afb5c95-c7b5-498a-abec-ae829d164964") - search results are improperly including documents whose
typeis "course" and whosecustom_keystarts with "LD_" - search results are improperly including documents whose
typeis "course" and whosecustom_keystarts with "user-SCORM_"
I'm genuinely not sure what I'm doing wrong here. I have also tried replacing the regexp subclauses with match subclauses (e.g. subFilter.filter('match', 'custom_key', 'LD_*'),
but I get the exact same results.
I've been staring at this thing for a literal week now, trying hundreds of slightly-different things similar to what is described in this post but this is the closest I've gotten to getting the results I need. What am I doing wrong?
