PostgreSQL Lookup

Provides functionality to lookup records in the target PostgreSQL table.

Overview

This Snap provides functionality to lookup records in the target Postgres table and returns a selected set of fields for every matched record. The Snap executes one request per multiple input documents to avoid making a request for every input record.

JSON paths can be used in the Snap properties and will have values from an incoming document substituted into the properties. However, documents missing values for a given JSON path will be written to the Snap's error view. After a query is executed, the query's results are merged into the incoming document.

The Snap ignores any duplicated lookup condition in the input document stream since it maintains a cache for lookup conditions internally.

Prerequisites

A valid PostgreSQL Account with the required permissions.

Support and Limitations

Works in Ultra Tasks if pass-through is enabled.

Snap views

Type Description Examples of upstream and downstream Snaps
Input This Snap allows exactly one document input view. Each document should have values for one AND clause in the WHERE statement.
Output This Snap has exactly one document output view. The output document includes the corresponding input data under the "original" key. If there are no results from the query, each output field will have a null value.
Learn more about Error handling.

Snap settings

Legend:
  • Expression icon (): Allows using JavaScript syntax to access SnapLogic Expressions to set field values dynamically (if enabled). If disabled, you can provide a static value. Learn more.
  • SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
  • Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
  • Upload : Uploads files. Learn more.
Learn more about the icons in the Snap settings dialog.
Field/Field set Type Description
Label

String

 Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline.

Default value: PostgreSQL - Lookup

Example: Lookup Employee
Schema name

String

 The database schema name. Selecting a schema filters the Table name list to show only those tables within the selected schema.
Note: The values can be passed using the pipeline parameters but not the upstream parameter.

Example: SYS

Default value: N/A
Table name

String

 Required. Enter or select the name of the table to execute the lookup query on.
Note: The values can be passed using the pipeline parameters but not the upstream parameter.

Example: people

Default value: N/A
Output fields

String

 Required. Enter or select output field names for SQL SELECT statement. If this property is empty, the Snap selects all fields by executing the statement "SELECT * FROM ...".

Example: email, address, first, last

Default value: N/A
Lookup conditions Required. The lookup conditions are created by using the lookup column name and the lookup column value. Each row will build a condition, such as lookupColumn1 = $inputField. Each additional row will be concatenated using a logical AND. All rows together build the lookup condition being used to lookup records in the lookup table.

Default value: N/A
Value

String/Expression

 Required. Enter or select the JSON path of the lookup column value. The value will be provided by the input data field.

Example: $email, $first, $last

Default value: N/A
Lookup column name

String

 Required. Enter or select lookup column name.

Example: email, first, last

Default value: N/A
Pass-through on no lookup match

Checkbox

 When there is no lookup matching an input document, the input document will pass through to the output view if this property is checked. Otherwise, it will be written to the error view as an error condition.

Default value: False
Snap Execution

Dropdown list

Choose one of the three modes in which the Snap executes. Available options are:

  • Validate & Execute: Performs limited execution of the Snap and generates a data preview during pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during pipeline runtime.
  • Execute only: Performs full execution of the Snap during pipeline execution without generating preview data.
  • Disabled: Disables the Snap and all Snaps that are downstream from it.

Default value: Execute only

Example: Validate & Execute