# Can I combine a single fields search results and get a hash?

**URL:** <https://discuss.elastic.co/t/can-i-combine-a-single-fields-search-results-and-get-a-hash/258212>\
**Category:** Elasticsearch\
**Created:** [December 10, 2020, 3:18am UTC](https://discuss.elastic.co/t/can-i-combine-a-single-fields-search-results-and-get-a-hash/258212 "2020-12-10T03:18:54Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ChristoKiwi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christokiwi/32/57060_2.png) [@ChristoKiwi](https://discuss.elastic.co/u/ChristoKiwi)\
**Post date:** [December 10, 2020, 3:18am UTC](https://discuss.elastic.co/t/can-i-combine-a-single-fields-search-results-and-get-a-hash/258212/1 "2020-12-10T03:18:54Z")

</div>

I have a large ES index (7.7) that has a couple of million rows.  
I want to be able to pull a days worth of data and using their UUID id values generate a hash (MD5 or SHA1 ideally).

I can do this easily using SQL:

```auto
SELECT MD5(CAST(ARRAY_AGG(m.id ORDER BY id) AS TEXT)) FROM message m WHERE m.created >= '2020-12-09' AND m.created < '2020-12-10';

```

(Returns: 'e54b3fcdb7e9b9fbfef7c5e7785a9eg0')

But combining or aggregating a single column into a long string and then returning a hash of that value is proving to be difficult using queries or painless scripting techniques.

Mapping (simplified):

```auto
curl -X PUT http://localhost:9200/test-docker/_mapping -H "Content-Type: application/json" -d '{"properties":{"id":{"type":"text"},"created":{"type":"date_nanos"},"application":{"type":"keyword"},"content":{"type":"text"}}}'

```

Closest I have come is (Note, I haven't hashed or refined by date range yet):

```auto
curl -X GET "localhost:9200/_search?pretty" -H 'Content-Type: application/json' -d'
{
    "query": {
        "match_all": {}
    },
    "script_fields": {
        "test": {
            "script": {
            	"lang": "painless",
            	"source": "String agg = \"\";\nfor (int i = 0; i < doc[\u0027_id\u0027].length; i++) {\nagg += doc[\u0027_id\u0027][i];if (i >= doc[\u0027_id\u0027].length) {\nagg += \",\";\n}\n}\nreturn agg;"
            }
        }
    },
    "sort" : [
        { "created" : {"order" : "asc"}}
    ]
}'

```

But this continues to return every document individually with just one UUID value in the returned result:

```auto
"hits" : [
	{
		"_index" : "mobile-report-docker",
		"_type" : "_doc",
		"_id" : "4ff783b2-fe77-4c17-8000-370f47fb656c",
		"_score" : null,
		"fields" : {
		  "test" : [
		    "4ff783b2-fe77-4c17-8000-370f47fb656c"
		  ]
		},
		"sort" : [
		  1607506728626093585
		]
	},
	{
... etc

```

---

<div class="post-metadata">

**Author:** ![Iker](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iker/32/91708_2.png) [@Iker](https://discuss.elastic.co/u/Iker)\
**Post date:** [December 10, 2020, 5:06am UTC](https://discuss.elastic.co/t/can-i-combine-a-single-fields-search-results-and-get-a-hash/258212/2 "2020-12-10T05:06:59Z")

</div>

That is not very optimal, you could end with a string that is just too long for handle in the script, try to update the hash function for every record in the script that you have iterating the documents updating the hash with the next element; there are no crypto implementations in painless as far as I know, but you could use one from the internet.

> <https://stackoverflow.com/questions/46180650/sha1-in-painless-elasticsearch>

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 7, 2021, 5:07am UTC](https://discuss.elastic.co/t/can-i-combine-a-single-fields-search-results-and-get-a-hash/258212/3 "2021-01-07T05:07:34Z")

</div>

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