lookup command - Amazon OpenSearch Service

lookup command

Note

To see which AWS data source integrations support this PPL command, see Commands.

Use the lookup command to enrich your search data by adding or replacing data from a lookup index (dimension table). This command allows you to extend fields of an index with values from a dimension table. You can also use it to append or replace values when lookup conditions are met. The lookup command is more suitable than the Join command for enriching source data with a static dataset.

Syntax

Use the following syntax:

SEARCH source=<sourceIndex>
| <other piped command>
| LOOKUP <lookupIndex> (<lookupMappingField> [AS <sourceMappingField>])...
    [(REPLACE | APPEND) (<inputField> [AS <outputField>])...]
| <other piped command>
lookupIndex

  • Required.

  • The name of the lookup index (dimension table).

lookupMappingField

  • Required.

  • A mapping key in the lookup index, analogous to a join key from the right table. You can specify multiple fields, separated by commas.

sourceMappingField

  • Optional.

  • Default: <lookupMappingField>.

  • A mapping key from the source query, analogous to a join key from the left side.

inputField

  • Optional.

  • Default: All fields of the lookup index where matched values are found.

  • A field in the lookup index where matched values are applied to the result output. You can specify multiple fields, separated by commas.

outputField

  • Optional.

  • Default: <inputField>.

  • A field in the output. You can specify multiple output fields. If you specify an existing field name from the source query, its values will be replaced or appended by matched values from inputField. If you specify a new field name, it will be added to the results.

REPLACE | APPEND

  • Optional.

  • Default: REPLACE

  • Specifies how to handle matched values. If you specify REPLACE, matched values in <lookupIndex> field overwrite the values in result. If you specify APPEND, matched values in <lookupIndex> field only append to the missing values in result.

Usage

  • LOOKUP <lookupIndex> id AS cid REPLACE mail AS email

  • LOOKUP <lookupIndex> name REPLACE mail AS email

  • LOOKUP <lookupIndex> id AS cid, name APPEND address, mail AS email

  • LOOKUP <lookupIndex> id

Example

See the following examples.

SEARCH source=<sourceIndex>
| WHERE orderType = 'Cancelled'
| LOOKUP account_list, mkt_id AS mkt_code REPLACE amount, account_name AS name
| STATS count(mkt_code), avg(amount) BY name
SEARCH source=<sourceIndex>
| DEDUP market_id
| EVAL category=replace(category, "-", ".")
| EVAL category=ltrim(category, "dvp.")
| LOOKUP bounce_category category AS category APPEND classification    
SEARCH source=<sourceIndex>
| LOOKUP bounce_category category