Last active
November 17, 2025 09:46
-
-
Save xeraa/7b6f1ec6210bb36129fbdad6e6a5ab9d to your computer and use it in GitHub Desktop.
ES|QL JOIN on two fields
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DELETE main | |
| PUT main/_doc/1 | |
| { | |
| "timestamp": "2025-10-12T00:00:00", | |
| "symbol": "ESTC", | |
| "price": 10.5 | |
| } | |
| PUT main/_doc/2 | |
| { | |
| "timestamp": "2025-10-13T00:00:00", | |
| "symbol": "ESTC", | |
| "price": 10.0 | |
| } | |
| PUT main/_doc/3 | |
| { | |
| "timestamp": "2025-10-14T00:00:00", | |
| "symbol": "TSLA", | |
| "price": 15.5 | |
| } | |
| DELETE joined | |
| PUT joined | |
| { | |
| "settings": { | |
| "index.mode": "lookup" | |
| } | |
| } | |
| PUT joined/_doc/1 | |
| { | |
| "timestamp": "2025-10-14T00:00:00", | |
| "symbol": "ESTC", | |
| "start_price": 11.5 | |
| } | |
| PUT joined/_doc/2 | |
| { | |
| "timestamp": "2025-10-13T00:00:00", | |
| "symbol": "ESTC", | |
| "start_price": 11.0 | |
| } | |
| POST /_query?format=txt | |
| { | |
| "query": """ | |
| FROM main | |
| | LOOKUP JOIN joined ON timestamp, symbol.keyword | |
| | LIMIT 10 | |
| """ | |
| } | |
| DELETE joined_enriched | |
| PUT joined_enriched | |
| { | |
| "settings": { | |
| "index.mode": "lookup" | |
| } | |
| } | |
| POST _reindex | |
| { | |
| "source": { | |
| "index": "joined" | |
| }, | |
| "dest": { | |
| "index": "joined_enriched" | |
| }, | |
| "script": { | |
| "source": "ctx._source.combined = ctx._source.symbol + ctx._source.timestamp.toString() + '.000Z'" | |
| } | |
| } | |
| POST /_query?format=txt | |
| { | |
| "query": """ | |
| FROM main | |
| | RENAME symbol.keyword AS main_symbol, timestamp AS main_timestamp | |
| | EVAL combined.keyword = CONCAT(main_symbol, main_timestamp::STRING) | |
| | LOOKUP JOIN joined_enriched ON combined.keyword | |
| | LIMIT 10 | |
| """ | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment