lookup

Looks up a mapping table using the key field value of input records and assigns the mapped value to output fields.

Command properties

Item	Description
Command type	Processing query
Required permission	Read permission on the mapping table
License usage	N/A
Parallel execution	Supported
Distributed execution	Runs on Data Node (mapper)

Syntax

lookup TABLE SRC_FIELD [as LOOKUP_KEY] output FIELD [as ALIAS], ...

Target

TABLE: Name of the mapping table to look up
SRC_FIELD [as LOOKUP_KEY]: Field in the input record to use as the lookup key. When the key field name in the mapping table differs, you can specify an alias using as. If as is omitted, the input field name is used directly as the key field name in the mapping table.
output FIELD [as ALIAS], ...: List of output fields to retrieve from the mapping table. Multiple fields can be specified separated by commas (,). You can change the field name assigned to the input record using as. If as is omitted, the mapping table's field name is used as-is.

Error codes

Parse errors

Error code	Message	Description
`20700`	lookup 명령에서 output 구문이 누락되었습니다.	When the `output` keyword is not specified
`20701`	lookup 명령에서 [table]은 유효하지 않는 매핑 테이블입니다.	When a non-existent mapping table name is specified
`20702`	lookup 필드 문법이 유효하지 않습니다. `필드 as 별칭` 문법을 사용하십시오.	When the field alias syntax is incorrect
`20703`	lookup 명령의 [as] 자리에 as가 와야 합니다.	When a token other than `as` is specified in the `as` position
`20704`	[name]에 대한 lookup 권한이 없습니다.	When there is no read permission for the mapping table

Runtime errors

N/A

Description

The lookup command uses the key field value of input records to look up a mapping table and assigns the mapped value to output fields. If the key does not exist in the mapping table, null is assigned to the output fields. If no mapping table handler is registered, input records are output as-is without any lookup.

When the input field name and the mapping table's key field name differ, you can specify the mapping table's key field using as. Similarly, output field names can be changed using as.

Examples

Look up a single field from a mapping table
```
json "[{'src_ip': '192.0.2.1'}, {'src_ip': '192.0.2.2'}, {'src_ip': '192.0.2.3'}]"
| lookup ip_asset src_ip output hostname
```
Looks up the hostname field from the ip_asset mapping table using the src_ip field value as the key and adds it to the input record.
Look up multiple fields from a mapping table
```
json "[{'src_ip': '192.0.2.1'}, {'src_ip': '192.0.2.2'}]"
| lookup ip_asset src_ip output hostname, department
```
Simultaneously retrieves the hostname and department fields from the ip_asset mapping table.
Use an alias for the key field
```
json "[{'host': '192.0.2.1'}, {'host': '192.0.2.2'}]"
| lookup ip_asset host as ip output hostname
```
Uses the host field value in the input record as the ip key field in the mapping table for lookup.
Use aliases for output fields
```
json "[{'src_ip': '192.0.2.1'}, {'src_ip': '192.0.2.2'}]"
| lookup ip_asset src_ip output hostname as src_host, department as src_dept
```
Assigns the hostname field from the mapping table as src_host and the department field as src_dept to the input record.