lookup command

The lookup command enriches search results with fields from a second index, referred to as a lookup index or dimension table. For each row in the search results, the lookup command finds the matching row in the lookup index and copies fields from that row into the result.

The lookup command functions like a left join: every row in the search results is kept, and matched fields from the lookup index are added or overwritten. If a row has no match, replace sets the copied fields to null, and append keeps any existing values. Compared with the join command, lookup is simpler and better suited for adding static reference data, such as region names, user departments, or product categories, to search results.

Syntax

The lookup command has the following syntax:

lookup <lookupIndex> (<lookupMappingField> [as <sourceMappingField>])... [(replace | append | output) (<inputField> [as <outputField>])...]

The following table describes example lookup commands.

Parameters

The lookup command supports the following parameters.

Matching rows by field

The <lookupMappingField> and <sourceMappingField> parameters use the format <lookupMappingField> AS <sourceMappingField>. The field to the left of AS is in the lookup index, and the field to the right of AS is in the search results.

For example, lookup work_information uid AS id matches each row in the search results whose id equals the uid of a row in work_information. If both indexes use the same field name, omit AS. For example, lookup work_information id matches id in the search results to id in the lookup index.

To match on multiple fields, specify them as a comma-separated list. A row matches only when all of the fields are equal. You can combine renamed and same-name fields in one list. For example, lookup work_information uid AS id, name matches when uid equals id and the name values are equal.

Selecting fields to copy

The <inputField> and <outputField> parameters follow the match fields and use the format <inputField> AS <outputField>. The field to the left of AS is in the lookup index and supplies the value. The field to the right of AS is the name of the field in the search results. For example, replace department AS dept copies department from the lookup index into a field named dept in the search results.

If you omit AS, the field keeps its name from the lookup index. If you omit <inputField>, lookup copies all fields from the lookup index except the match fields.

Handling existing fields

The replace, append, and output keywords specify the replacement strategy, which determines how a copied value is applied when the output field already exists in the result row. The following table compares the replace and append strategies.

If the output field does not exist in the search results, both strategies add it. The output keyword is a synonym for replace.

Examples

The following examples enrich a worker index using a work_information lookup index.

The worker index contains the following documents.

The work_information index contains the following documents. Its key field is uid, and it contains no rows for workers 1001 and 1004.

Example 1: Copying one field using differently named match fields

The following query matches each worker whose id equals a uid in work_information and copies department into the results. Because worker does not contain a department field, replace adds it. Rows without a match contain null:

source = worker
  | lookup work_information uid AS id REPLACE department
  | fields id, name, occupation, country, salary, department
  | sort id

The query returns the following results:

Example 2: Comparing replace and append for an existing field

The replace and append strategies produce different results only when the output field already contains values. The following queries copy department from the lookup index into the existing country field.

Using replace, the lookup value overwrites country in every matched row. In rows without a match, country is overwritten with null:

source = worker
  | lookup work_information uid AS id REPLACE department AS country
  | fields id, name, occupation, salary, country
  | sort id

The query returns the following results:

Using append, existing country values are kept, and the lookup value is set only in rows in which country is null:

source = worker
  | lookup work_information uid AS id APPEND department AS country
  | fields id, name, occupation, salary, country
  | sort id

The query returns the following results:

Only the row for worker 1003 changed: its country was null, so append set it to HR.

Example 3: Copying all fields

If you omit <inputField>, lookup copies every field in the lookup index except the match fields. The following query matches on both uid AS id and name, so department and occupation are copied:

source = worker
  | lookup work_information uid AS id, name
  | fields id, name, occupation, country, salary, department
  | sort id

The query returns the following results:

Because worker already contains an occupation field and the default strategy is replace, the occupation value from the lookup index overwrites it. For example, Doctor becomes Scientist for worker 1002, and Artist becomes null for worker 1001.

Example 4: Copying a field into a new field

If the output field does not exist in the search results, lookup adds it and leaves the existing fields unchanged. The following query matches on name and copies occupation from the lookup index into a new new_col field:

source = worker
  | lookup work_information name REPLACE occupation AS new_col
  | fields id, name, occupation, country, salary, new_col
  | sort id

The query returns the following results:

Both rows for David (1003 and 1004) contain Doctor in new_col because both match the same lookup row by name.

Example 5: Using the output keyword

The output keyword is a synonym for replace. The following query returns the same results as Example 1:

source = worker
  | lookup work_information uid AS id OUTPUT department
  | fields id, name, occupation, country, salary, department
  | sort id