Streaming Data Transform Functions and Operators

Extracts values from a JSON string value into a table with specified columns. Can be used to create more than one table.

Use these values inside the function.

• Field (string)—The name of the field in the input object where the JSON values are stored. It can be any expression that results in a JSON string value.
• rootPath (path)—The JSON path expression that locates properties in a JSON object.
• List (ColumnName Type PATH Path)—A comma-separated list of columns that is within the table.
  • ColumnName (string)—The name of the column in the table returned from the JSON_TABLE invocation.
  • Type—The type of data being outputted. Valid values are TEXT or NUMBER.
  • Path (path)—The JSON path expression that locates properties in a JSON object in relation to the root path.
• Alias (string)—The name of the table being created.

Examples

SELECT 
  customers_with_orders__dll.order_id__c as order_id__c, 
  customers.customer as customer__c
FROM customers_with_orders__dll, JSON_TABLE(
  customers_with_orders__dll.json_data__c, '$' COLUMNS (
 	customer TEXT PATH '$.customer'
	)
) customers

SELECT 
  customers_with_orders__dll.order_id__c as order_id__c, 
  items.item_name as item_name__c,
  items.item_price as item_price__c
FROM customers_with_orders__dll, JSON_TABLE(
  customers_with_orders__dll.json_data__c, '$.items[*]' COLUMNS (
    item_name TEXT PATH '$.name',
    item_price NUMBER PATH '$.price'
  )
) items

Returns true if the text matches the regular expression pattern, and false otherwise.Use these values with the operator.

• text (string): The text to search.
• pattern (string): A regular expression.

Example

REGEXP(order_id__c, '^[A-Z]{3}-[0-9]+$')

Returns the matching group at the specified index from the text. Index 0 returns the entire match. Index 1 and higher return capturing groups, which are the parts of the pattern enclosed in parentheses.

Use these values inside the function.

• text (string): The text to search.
• pattern (string): A regular expression.
• index (number): The zero-based index of the group to return.

Example

REGEXP_EXTRACT(order_id__c, '([A-Z]+)-([0-9]+)', 1)For an order_id__c value of ORD-1042, this example returns ORD.

Returns the datetime value for a date, date time, or epoch expression.

Examples

Date

TO_DATETIME(2023-05-15) returns 2023-05-15 00:00:00

Epoch

TO_DATETIME(1700127401) returns 2023-11-16 09:36:41

Returns a date value for a date, datetime, or epoch expression.

Examples

Datetime

TO_DATE(2023-11-16 09:36:41) returns 2023-11-16

Epoch

TO_DATE(1700127401) returns 2023-11-16

ISNULL()

IFNULL()

NULLIF()

ISNOTNULL()

Don’t compare a field to null using =,!=, or <>. These comparisons don’t match null values, so rows with a null in that field will be silently excluded or included when you don’t expect it. Always use field ISNULL(field) or field ISNOTNULL(field) instead.

These functions test for null values.

For number and datetime data types:

• To check for a non-null value, use ISNOTNULL(field).
• To check for a null value, use ISNULL(field).

Returns a value when the first condition is met, such as if-then-else.

Example

SELECT 
    SourceObject.Id as id__c,
    CASE
        WHEN upper(SourceObject.Type) LIKE '%CAT%' THEN 'pet'
        WHEN lower(SourceObject.Description) LIKE '%donut%' THEN 'food'
        ELSE 'Unknown'
    END AS newProduct__c
    FROM SourceObject

Logical AND checks to see that both conditions are true.

Example

SELECT *
FROM SourceObject
WHERE (Type <> "" AND Comment == "")

Logical OR checks to see if either one of the conditions is true.

Example

SELECT *
FROM SourceObject
WHERE (Type <> "" AND Comment == "")
    OR (FirstName == "Alice" AND LastName == "Bob")

Greater than (>)

Greater than or equal to (>=)

Less than (<)

Less than or equal to (<=)

Equal to (==)

Logical AND (&&)

Logical OR (||)

Logical NOT (!=, <>)

Combine up to five SELECT statements. Each SELECT statement must use the same source data lake object.

Example

SELECT Name, City
FROM AccountObject
UNION
SELECT Name, City
FROM AccountObject;