--- schema_version: "1" id: a6941ba0181179afef78b7b9ed1fc930f0181bee5577efa710706b0e4877a388 title: Streaming Data Transform Functions and Operators collection: admin version: current locale: en-us portal_url: https://docs.salesforce.com/admin/current/en-us/data/264-0-0/c360_a_streaming_transform_supported_functions.md canonical_url: https://help.salesforce.com/s/articleView?id=data.c360_a_streaming_transform_supported_functions.htm&release=264.0.0&type=5 source_site: help.salesforce.com content_type: documentation content_hash: ab2cbe3df24eb6a98672717343958d386ba15de03b9705aa7e30309db87b39df description: You can use these functions and operators in a streaming data transform SQL statement. products: - Data 360 release: "264" --- # Streaming Data Transform Functions and Operators You can use these functions and operators in a streaming data transform SQL statement.
Category Function Name Description
Table JSON_TABLE(field, rootPath COLUMNS (list) alias)

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, '

#### See Also

- [Data Types in Data 360](https://help.salesforce.com/s/articleView?id=data.c360_a_data_types.htm&release=264.0.0&type=5 "Data 360 supports these data types.")
- [Date and Datetime Formats](https://help.salesforce.com/s/articleView?id=data.c360_a_data_types_and_date_formats.htm&release=264.0.0&type=5 "Data 360 supports date and datetime data types.")
- [Normalize Data with UNION Use Case](https://help.salesforce.com/s/articleView?id=data.c360_a_streaming_transform_normalization.htm&release=264.0.0&type=5 "Normalize data ingested into Data 360 with streaming data transforms.") 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
Text CONCAT() Returns a concatenated string of the provided values. Accepts two or more strings or field API names.
Text COALESCE Returns the first value from the list that isn’t empty.
Text TRIM(text) Removes all spaces from the text except for single spaces between words.
Text RTRIM(text) Removes all spaces from the end.
Text LOWER(text) Converts all uppercase letters in a text string to lowercase.
Text UPPER(text) Converts all lowercase letters in a text string to uppercase.
Text TO_TEXT(expr) Returns the UTF-8 value for a text, number, boolean, date, or datetime expression.
Text REGEXP(text, pattern)

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]+ #### See Also - [Data Types in Data 360](https://help.salesforce.com/s/articleView?id=data.c360_a_data_types.htm&release=264.0.0&type=5 "Data 360 supports these data types.") - [Date and Datetime Formats](https://help.salesforce.com/s/articleView?id=data.c360_a_data_types_and_date_formats.htm&release=264.0.0&type=5 "Data 360 supports date and datetime data types.") - [Normalize Data with UNION Use Case](https://help.salesforce.com/s/articleView?id=data.c360_a_streaming_transform_normalization.htm&release=264.0.0&type=5 "Normalize data ingested into Data 360 with streaming data transforms."))
Text REGEXP_EXTRACT(text, pattern, index)

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.
Datetime TO_DATETIME(expr)

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
Datetime HOUR_ADD Adds a number of hours to the datetime value based on the integer value of the second function parameter. Returns the datetime that is the number of hours after the start datetime.
Datetime HOUR_SUB Subtracts a number of hours from the datetime value based on the integer value of the second function parameter. Returns the date that is the number of hours before the start datetime.
Boolean TO_BOOLEAN(expr) Returns a boolean value. Accepts boolean values or the text values “True” and “False.”
Number TO_NUMBER(expr) Returns a number value for a given expression. Returns null for text, date, datetime, and boolean values.
Date TO_DATE(expr)

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
Logic

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 the text data type:

  • For the Text data type, null values and empty strings are different. A double quote without characters between them represents an empty string.""
  • To exclude both empty strings and null values, use field <> "" AND ISNOTNULL(field).

    In Data Explorer, both empty strings and null values are displayed as NULL. To ensure accurate filtering, it’s recommended to include checks for both null values and empty strings.

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).
Logic CASE WHEN statement

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
Logic AND(condition1, condition2)

Logical AND checks to see that both conditions are true.

Example

SELECT *
FROM SourceObject
WHERE (Type <> "" AND Comment == "")
Logic OR(condition1, condition2)

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")
Operators Logical operators

Greater than (>)

Greater than or equal to (>=)

Less than (<)

Less than or equal to (<=)

Equal to (==)

Logical AND (&&)

Logical OR (||)

Logical NOT (!=, <>)
Operators UNION

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;

To ensure records are properly updated, validate that none of the following conditions are present at the same time.

  • A column across two or more SELECT blocks in the UNION evaluate to the same value; and
  • The column is used as the primary key in the target object; and
  • The SELECT blocks contain predicates that filter the data, and each predicate filters the data differently
Date & Datetime CURRENT_DATE() Returns the current UTC date as a value of the date data type.
#### See Also - [Data Types in Data 360](https://help.salesforce.com/s/articleView?id=data.c360_a_data_types.htm&release=264.0.0&type=5 "Data 360 supports these data types.") - [Date and Datetime Formats](https://help.salesforce.com/s/articleView?id=data.c360_a_data_types_and_date_formats.htm&release=264.0.0&type=5 "Data 360 supports date and datetime data types.") - [Normalize Data with UNION Use Case](https://help.salesforce.com/s/articleView?id=data.c360_a_streaming_transform_normalization.htm&release=264.0.0&type=5 "Normalize data ingested into Data 360 with streaming data transforms.")