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, '$' 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.
Datetime
TO_DATETIME(expr)
Returns the datetime value for a date, date time, or epoch
expression.
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()
These functions test for null values.
For the text data type:
To check for a null value, use field =
''.
For the Text data type,
null values and empty strings are different. A double quote without characters
between them represents an empty string.""
To check for a non-null value, use field <>
''.
To exclude both empty strings and null values, use field <> "" AND 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 WHENstatement
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.
We use three kinds of cookies on our websites: required, functional, and advertising. You can choose whether functional and advertising cookies apply. Click on the different cookie categories to find out more about each category and to change the default settings.
Privacy Statement
Required Cookies
Always Active
Required cookies are necessary for basic website functionality. Some examples include: session cookies needed to transmit the website, authentication cookies, and security cookies.
Functional Cookies
Functional cookies enhance functions, performance, and services on the website. Some examples include: cookies used to analyze site traffic, cookies used for market research, and cookies used to display advertising that is not directed to a particular individual.
Advertising Cookies
Advertising cookies track activity across websites in order to understand a viewer’s interests, and direct them specific marketing. Some examples include: cookies used for remarketing, or interest-based advertising.
