# Dec 14th, 2023: \[EN\] A Peak Inside Santa's Planning Meeting - Using ES|QL for Data Enrichment
**URL:** https://discuss.elastic.co/t/dec-14th-2023-en-a-peak-inside-santas-planning-meeting-using-es-ql-for-data-enrichment/348131
**Category:** Advent Calendar
**Created:** [December 14, 2023, 8:00am UTC](https://discuss.elastic.co/t/dec-14th-2023-en-a-peak-inside-santas-planning-meeting-using-es-ql-for-data-enrichment/348131 "2023-12-14T08:00:53Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@Alexis\_Roberson](https://discuss.elastic.co/u/Alexis_Roberson)
#### Post date: [December 14, 2023, 8:00am UTC](https://discuss.elastic.co/t/dec-14th-2023-en-a-peak-inside-santas-planning-meeting-using-es-ql-for-data-enrichment/348131/1 "2023-12-14T08:00:53Z")
Often, we hear of Santa’s list, but what if the Elves had a list as well? With a little imagination, we could assume Santa’s list contains the names of children and whether they were naughty or nice and the Elves list would contain the name of the toy to be delivered as well as the mode of delivery such as chimney or fire escape. Santa and his elves must come together to compare both lists to ensure only toys for kids with the Nice checkmark are built.
In the monthly planning meeting, they decide it would be more efficient to create a new list with data from both lists. This will ensure the Elves know which child should receive a toy and Santa knows which toy should be delivered along with the mode of delivery.
One of the Elves suggests using [ES|QL](https://www.elastic.co/blog/esql-elasticsearch-piped-query-language) to enrich the data by adding data from both lists for only the children who were “Nice” this year.
ES|QL is a piped query language that allows output from one command to be passed as input into another command. This makes it great for filtering, searching, transforming, and aggregating your data while still maintaining simplicity.
Santa’s list could look something like this:
```auto
address | first_name | id | last_name | status
------------------+---------------+---------------+---------------+---------------
123 Perry Ave |Maya |12045 |Nichols |Nice
256 Jefferson Pike|Jacob |12046 |Meyers |Naughty
246 Washhington St|Tonya |12047 |Speed |Nice
357 Pinecone Rd |Peter |12048 |Marshall |Nice
123 Perry Ave |Mia |12049 |Nichols |Naughty
```
And the Elves list could like this:
```auto
delivery_method| id | toy_name
---------------+---------------+---------------
Chimney |12045 |bike
Fire escape |12046 |Ninja Turtles
Chimney |12047 |Easy bake oven
Fire escape |12048 |Magician kit
Chimney |12049 |Prankster Kit
```
To do this, we only need to create an enrichment policy.
**Index documents**
First, let’s create the mapping for Santa’s list:
```auto
PUT santas_list
{
"mappings": {
"properties": {
"id": {
"type": "keyword"
},
"first_name": {
"type": "keyword"
},
"last_name": {
"type": "keyword"
},
"address": {
"type": "keyword"
},
"status": {
"type": "keyword"
}
}
}
}
```
It’s important to note, the ES|QL enrich command only supports enrich policies of type match. This means the request must be an exact match, taking into account case sensitivity. Therefore enrich only supports enriching on a column of type [keyword](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-enrich-data.html#_limitations_8).
Something else to keep in mind is the naming of the id field. It is tempting to use something like `_id` as the name, but this will result in the field not being returned as expected. More specifically, [`_id` field](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-id-field.html) is restricted from use in aggregations, sorting and scripting. The advice here is to duplicate the content of the `_id` field into another field that has doc\_values.
Now that we have defined our index mapping, let’s add some data:
```auto
PUT santas_list/_bulk
{"index": {}}
{"id": 12045, "first_name": "Maya", "last_name": "Nichols", "address": "123 Perry Ave", "status": "Nice"}
{"index": {}}
{"id": 12046, "first_name": "Jacob", "last_name": "Meyers", "address": "256 Jefferson Pike", "status": "Naughty"}
{"index": {}}
{"id": 12047, "first_name": "Tonya", "last_name": "Speed", "address": "246 Washhington St", "status": "Nice"}
{"index": {}}
{"id": 12048, "first_name": "Peter", "last_name": "Marshall", "address": "357 Pinecone Rd", "status": "Nice"}
{"index": {}}
{"id": 12049, "first_name": "Mia", "last_name": "Nichols", "address": "123 Perry Ave", "status": "Naughty"}
```
Now, let’s create the mapping for the Elves list:
```auto
PUT elves_toy_backlog
{
"mappings": {
"properties": {
"id": {
"type": "keyword"
},
"toy_name": {
"type": "keyword"
},
"delivery_method": {
"type": "keyword"
}
}
}
}
```
Now, we can add the data:
```auto
PUT elves_toy_backlog/_bulk
{"index": {}}
{"id": 12045, "toy_name": "bike", "delivery_method": "Chimney"}
{"index": {}}
{"id": 12046, "toy_name": "Ninja Turtles", "delivery_method": "Fire escape"}
{"index": {}}
{"id": 12047, "toy_name": "Easy bake oven", "delivery_method": "Chimney"}
{"index": {}}
{"id": 12048, "toy_name": "Magician kit", "delivery_method": "Fire escape"}
{"index": {}}
{"id": 12049, "toy_name": "Prankster Kit", "delivery_method": "Chimney"}
```
**Create enrich policy**
For the enrich policy, we want to use the id field as our match\_field to merge the two lists. For our enriched fields, we will be adding toy\_name and delivery\_method.
```auto
PUT /_enrich/policy/christmas-planning-meeting-policy
{
"match": {
"indices": "elves_toy_backlog",
"match_field": "id",
"enrich_fields": ["toy_name", "delivery_method"]
}
}
```
Next, we need to execute the policy:
```auto
PUT /_enrich/policy/christmas-planning-meeting-policy/_execute?wait_for_completion=false
```
**Execute ES|QL query**
Finally, we can run the query:
```auto
POST /_query?format=txt
{
"query": """
FROM santas_list
| WHERE status LIKE "Nice"
| KEEP first_name, last_name, id, status
| EVAL id
| ENRICH christmas-planning-meeting-policy ON id WITH toy_name, delivery_method
"""
}
```
Here, we select only the children who have a status of “Nice”. We then add the first\_name, last\_name, id, and status field from Santa’s list. Then, we’re able to add a new id column and use the enrich policy to add the toy\_name and delivery method.
The final result should look like this:
```auto
first_name | last_name | status | id | toy_name |delivery_method
---------------+---------------+---------------+---------------+---------------+---------------
Maya |Nichols |Nice |12045 |bike |Chimney
Tonya |Speed |Nice |12047 |Easy bake oven |Chimney
Peter |Marshall |Nice |12048 |Magician kit |Fire escape
```
**Updating/Changing Enrich Policy**
Unfortunately, you can’t update or change an enrich policy. Instead, you can:
1. Create and [execute](https://www.elastic.co/guide/en/elasticsearch/reference/current/execute-enrich-policy-api.html) a new enrich policy.
2. Replace the previous enrich policy with the new enrich policy in any in-use enrich processors or ES|QL queries.
3. Use the [delete enrich policy](https://www.elastic.co/guide/en/elasticsearch/reference/current/delete-enrich-policy-api.html) API or [Index Management in Kibana](https://www.elastic.co/guide/en/elasticsearch/reference/current/index-mgmt.html#manage-enrich-policies) to delete the previous enrich policy.
**Conclusion**
In this blog, we created an enrich policy to combine data from two indices. We then, implemented the policy within an ES|QL request that gave our final table.
Now, the Elves can get started building the toys for all the children on Santa's list marked as "Nice" and Santa knows which toys to deliver to each child.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [January 11, 2024, 8:01am UTC](https://discuss.elastic.co/t/dec-14th-2023-en-a-peak-inside-santas-planning-meeting-using-es-ql-for-data-enrichment/348131/2 "2024-01-11T08:01:34Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.