# How to write the following Elastic Search into advanced script query? **URL:** \ **Category:** Elasticsearch\ **Tags:** painless\ **Created:** [January 15, 2024, 8:51pm UTC](https://discuss.elastic.co/t/how-to-write-the-following-elastic-search-into-advanced-script-query/351111 "2024-01-15T20:51:47Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![ElasticDev1](https://avatars.discourse-cdn.com/v4/letter/e/db5fbb/32.png) [@ElasticDev1](https://discuss.elastic.co/u/ElasticDev1)\ **Post date:** [January 15, 2024, 8:51pm UTC](https://discuss.elastic.co/t/how-to-write-the-following-elastic-search-into-advanced-script-query/351111/1 "2024-01-15T20:51:47Z")
```auto { "took": 13, "timed_out": false, "_shards": { "total": 5, "successful": 5, "skipped": 0, "failed": 0 }, "hits": { "total": { "value": 2, "relation": "eq" }, "max_score": 1.0, "hits": [ { "_index": "car_db", "_type": "_doc", "_id": "11", "_score": 1.0, "_source": { "searchResults": [ { "beginTime": "2024-01-10T10:00:00+00:00", "nicknames": ["a_random_value"], "id": 6, "automobileId": 11, "finishedTime": "2024-01-20T10:00:00+00:00" } ] } }, { "_index": "car_db", "_type": "_doc", "_id": "13", "_score": 1.0, "_source": { "searchResults": [ { "beginTime": "2024-01-22T10:00:00+00:00", "nicknames": ["a_random_value"], "id": 8, "automobileId": 13, "finishedTime": "2024-01-25T10:00:00+00:00" } ] } } ] } } ``` If I use: ```auto GET /car_db/_search { "_source": ["searchResults"], "query": { "exists": { "field": "searchResults" } }, "size": 1000 } ``` I am able to get the list and content as expected. However I need to use scriptQuery because I am trying to do more advanced stuff. However once I use script query I can not get the content of the searchResults. type or paste code here ```auto ``` How can I achieve the same response using something like: ```auto GET /car_db/_search { "_source": true, "query": { "bool": { "filter": { "script": { "script": { "source": """ if (doc.containsKey('searchResults') && doc['searchResults'].size() > 0) { return true; } else { return false; } """ } } } } } } ``` Ofcourse once I get that to work I will modify it for more advanced operations. type or paste code here ```auto ``` ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [January 16, 2024, 6:14am UTC](https://discuss.elastic.co/t/how-to-write-the-following-elastic-search-into-advanced-script-query/351111/2 "2024-01-16T06:14:03Z")
I would recommend using the query DSL to as great extent as possible as it is likely to be faster and scale better than a complex script query that does it all, which can be very slow and expensive. For the example you gave I would recommend using an [exists query clause](https://www.elastic.co/guide/en/elasticsearch/reference/8.11/query-dsl-exists-query.html). It would probably help if you could describe what you are looking to achieve and provide some details about your use case. What is it you are trying to achieve that query DSL can not support? ---
**Author:** ![ElasticDev1](https://avatars.discourse-cdn.com/v4/letter/e/db5fbb/32.png) [@ElasticDev1](https://discuss.elastic.co/u/ElasticDev1)\ **Post date:** [January 16, 2024, 12:29pm UTC](https://discuss.elastic.co/t/how-to-write-the-following-elastic-search-into-advanced-script-query/351111/3 "2024-01-16T12:29:36Z")
With the scriptQuery I am looping through a field which contains an array of objects. Inside the array of object I need to compare it's value to another field in the same record/row, and if it matches that other field in the same record, the record should be returned in the response(so I return as true). However in my scriptQuery where I do a check with doc containskey, it does not find the key even though I use the same field name as listed by the mapping command: ```auto GET /car_db/_mapping?include_type_name=true ``` I wonder if this has anything to do with the fact that this field in Query Workbench as appears as searchResults{1} and it is an array of object. It is not nested any deeper than other field that I am able to make the script query work with. Only difference like I said is that it is stored inside {}. Mapping shows the fields are stored under properties. type or paste code here ```auto ``` ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [January 16, 2024, 1:03pm UTC](https://discuss.elastic.co/t/how-to-write-the-following-elastic-search-into-advanced-script-query/351111/4 "2024-01-16T13:03:36Z")
Is `searchResults` mapped as a nested field? If it is, the nested documents are store as separate documents behind the scenes so will not be accessible as doc values on the main document. I have not run any scripted queries on nested documents, but it is possible that you may need to access the source field, which is likely to be a lot slower. > [@ElasticDev1](#): > > With the scriptQuery I am looping through a field which contains an array of objects. Inside the array of object I need to compare it's value to another field in the same record/row, and if it matches that other field in the same record, the record should be returned in the response(so I return as true). Can you please provide a full example and explain why you are not able to do this using a nested query? ---
**Author:** ![ElasticDev1](https://avatars.discourse-cdn.com/v4/letter/e/db5fbb/32.png) [@ElasticDev1](https://discuss.elastic.co/u/ElasticDev1)\ **Post date:** [January 16, 2024, 2:19pm UTC](https://discuss.elastic.co/t/how-to-write-the-following-elastic-search-into-advanced-script-query/351111/5 "2024-01-16T14:19:06Z")
Okay so this is all part of my boolQuery filtering that I am implementing with PHP. The es index stores data in a field, under searchResults, in that searchResults { locationId, .....}(there is a lot more data in there), and that locationId needs to be compared with data that is sent from the endpoint request e.g. $data['location\_id']. If they match, than it means that record i.e. searchResults which is just a field in the row for an index of automobiles needs to be returned together with all the data in the row e.g. automobileId. ```auto protected function getAutomobiles(array $data): BoolQuery { $boolQuery = new BoolQuery(); if (isset($data['location_id'])) { $script = << 0) { def areaLocationId = doc.containsKey('areaLocationId') ? doc['areaLocationId'].value : null; def regionLocationId = doc.containsKey('regionLocationId') ? doc['regionLocationId'].value : null; for (locationId in locationIds) { if (result['scope'].contains('area') && areaLocationId != null && areaLocationId == locationId) { return true; } else if (result['scope'].contains('region') && regionLocationId != null && regionLocationId == locationId) { return true; } } } } } EOD; $scriptQuery = new ScriptQuery($script); $scriptQuery->addParameter( 'params', [ 'locationId' => $data['location_id'], ] ); $boolQuery->add($scriptQuery, BoolQuery::FILTER); } return $boolQuery; } ``` type or paste code here ```auto ``` ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [January 16, 2024, 2:31pm UTC](https://discuss.elastic.co/t/how-to-write-the-following-elastic-search-into-advanced-script-query/351111/6 "2024-01-16T14:31:33Z")
Please provide one or more sample documents (in JSON) and explain how you logically query for these. I am not a PHP developer and do not have time to try to interpret the logic from your script. ---
**Author:** ![ElasticDev1](https://avatars.discourse-cdn.com/v4/letter/e/db5fbb/32.png) [@ElasticDev1](https://discuss.elastic.co/u/ElasticDev1)\ **Post date:** [January 16, 2024, 3:28pm UTC](https://discuss.elastic.co/t/how-to-write-the-following-elastic-search-into-advanced-script-query/351111/7 "2024-01-16T15:28:37Z")
Thanks for your reply. Sure, here is the document in json: ```auto { "took": 398, "timed_out": false, "_shards": { "total": 5, "successful": 5, "skipped": 0, "failed": 0 }, "hits": { "total": { "value": 1, "relation": "eq" }, "max_score": 0.0, "hits": [ { "_index": "automobiles_db", "_type": "_doc", "_id": "14", "_score": 0.0, "_source": { "carBrand": "Toyota", "carEngine": "V6", "carPrice": 25000, "searchResults": [ { "beginTime": "2024-02-22T10:00:00+00:00", "locationIds": [ "23423" ], "id": 9, "finishedTime": "2024-02-25T10:00:00+00:00" }, { "beginTime": "2024-02-27T10:00:00+00:00", "locationIds": [ "324234" ], "id": 10, "finishedTime": "2024-02-28T10:00:00+00:00" } ] } } ] } } ``` Mapping: ```auto { "automobiles_db": { "mappings": { "properties": { "searchResults": { "properties": { "locationId": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "finishedTime": { "type": "date" }, "id": { "type": "long" }, "beginTime": { "type": "date" } } } } } } } ``` ---
**Author:** ![ElasticDev1](https://avatars.discourse-cdn.com/v4/letter/e/db5fbb/32.png) [@ElasticDev1](https://discuss.elastic.co/u/ElasticDev1)\ **Post date:** [January 19, 2024, 4:01pm UTC](https://discuss.elastic.co/t/how-to-write-the-following-elastic-search-into-advanced-script-query/351111/8 "2024-01-19T16:01:16Z")
So sum it up, the issue is, the field is stored as an array of objects, and the doc.containsKey('searchResults') seems to not exist. But it is there, I just can't access it. How? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [January 19, 2024, 4:22pm UTC](https://discuss.elastic.co/t/how-to-write-the-following-elastic-search-into-advanced-script-query/351111/9 "2024-01-19T16:22:18Z")
You are not using nested mappings, so Elasticsearch will index the values under the fields as arrays. ```auto "searchResults": [ { "beginTime": "2024-02-22T10:00:00+00:00", "locationIds": [ "23423" ], "id": 9, "finishedTime": "2024-02-25T10:00:00+00:00" }, { "beginTime": "2024-02-27T10:00:00+00:00", "locationIds": [ "324234" ], "id": 10, "finishedTime": "2024-02-28T10:00:00+00:00" } ] ``` will basically be indexed like this (simplified): ```auto "searchResults.beginTime": ["2024-02-22T10:00:00+00:00", "2024-02-27T10:00:00+00:00"], "searchResults.locationIds": ["23423", "324234"], "searchResults.id": [9, 10], "searchResults.finishedTime": ["2024-02-25T10:00:00+00:00", "2024-02-28T10:00:00+00:00"] ``` You should therefore be able to check if the document contains `searchResults.id` or one of the others, but as `searchResults` does not directly contain any terms this does not show up as a doc value. If you need access to the full structure you need to access `_source`, but be aware that can be very slow and scale badly. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 16, 2024, 4:22pm UTC](https://discuss.elastic.co/t/how-to-write-the-following-elastic-search-into-advanced-script-query/351111/10 "2024-02-16T16:22:50Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.