sonar-insert-lookup-record-batch

Inserts the input record into the specified lookup table. Records whose key already exists are skipped by default, and existing records are updated only when the update-fields option is specified.

Command properties

Syntax

Options

lookup=STR

The name of the target lookup table to insert records into

[update-fields=STR]

An option that determines whether to update the record when the key of the record you are inserting already exists. If you specify a value, a record with a duplicate key is updated in its entirety; if not specified, a record with a duplicate key is not inserted. The field names you specify do not limit the scope of the update — they are only used to check whether the fields exist in the target lookup schema — and you can specify multiple field names separated by commas (,)

Input fields

Output fields

Returns all fields of the input record with the following field added.

Error codes

Parsing errors

Runtime errors

N/A

Description

For each input record, the sonar-insert-lookup-record-batch command extracts values in the order of the fields defined in the lookup schema and inserts them into the lookup table. Each field value cannot exceed the maximum length defined in the lookup schema, and you cannot specify an empty value for a field that does not allow null.

If the key field value already exists in the lookup table, one of two behaviors occurs. If the update-fields option is not specified, the record is not inserted, and duplicated key. is recorded in the _error field. If the update-fields option is specified, the entire existing record is updated by replacing every field defined in the lookup schema with the values from the input record — not just the fields listed in update-fields.

The update-fields option does not limit which fields are updated. Any field not included in the input record is replaced with an empty value, erasing its existing value, so when updating a record you must always pass values for every field defined in the lookup schema.

If the key field value is missing (_error: key is null.) or is not a string type (_error: invalid key type.), the record is skipped and not processed. If per-field validation fails, a message in the form <field name> is empty. or <field name> exceed max len. is recorded in the _error field. These messages are always recorded in English regardless of locale.

If a record insertion or update fails at the database processing stage (for example, a duplicate key or a SQL error), that failure is not recorded in the _error field. The command only receives this stage's failure as a return value and does not reflect it in the output record, so even if the _error field is empty, the record may not actually have taken effect. Query the lookup table directly to confirm whether the change actually took effect.

To delete records, use the sonar-delete-lookup-record-batch command.

Examples

1. Insert records into a lookup table

   json "[{'ip': '192.0.2.10', 'asset_name': 'web-01'}, {'ip': '192.0.2.11', 'asset_name': 'web-02'}]"
   | sonar-insert-lookup-record-batch lookup="asset-ip-map"
   

   Inserts two records into the asset-ip-map lookup table. Records whose key value already exists are not inserted, and duplicated key. is recorded in the _error field.

2. Insert while updating a record with a duplicate key

   json "[{'ip': '192.0.2.10', 'asset_name': 'web-01-renamed'}]"
   | sonar-insert-lookup-record-batch lookup="asset-ip-map" update-fields="asset_name"
   

   For a record whose key already exists, instead of inserting it, the entire record is updated with the values from the input record. Because this example includes only the asset_name field in the input record, if the lookup schema has other fields besides asset_name, their existing values are cleared to empty strings. When updating, you must always pass values for every field defined in the lookup schema.

Change history

The sonar-insert-lookup-record-batch command is available in the Sonar 4.0 series starting with 4.0.2609.0, where the command previously provided by the Experimental app was built into Sonar core. On versions without the built-in command, install the Experimental app to use it. The Experimental app provides it under the name sonar-insert-lookup-records.