Skip to main content

coalesce (Function)

Returns the value of the first input parameter that is not null, and all input parameters have to be on the same type.

Syntax

<INT|LONG|DOUBLE|FLOAT|STRING|BOOL|OBJECT> coalesce(<INT|LONG|DOUBLE|FLOAT|STRING|BOOL|OBJECT> arg, <INT|LONG|DOUBLE|FLOAT|STRING|BOOL|OBJECT> ...)

Query Parameters

NameDescriptionDefault ValuePossible Data TypesOptionalDynamic
argThis function accepts one or more parameters. They can belong to any one of the available types. All the specified parameters should be of the same type.INT LONG DOUBLE FLOAT STRING BOOL OBJECTNoYes

Example 1

INSERT INTO barStream
SELECT coalesce('123', NULL, '789') AS value
FROM fooStream;

This query selects records from the fooStream collection and uses the coalesce function to return the first non-null value among the provided values ('123', NULL, '789'). In this case, the first non-null value is '123'. The result is aliased as value and inserted into the barStream.

Essentially, this query processes records in the fooStream and creates new records in the barStream with a constant value field set to '123'.

Example 2

INSERT INTO barStream
SELECT coalesce(NULL, 76, 567) AS value
FROM fooStream;

This query selects records from the fooStream collection and uses the coalesce function to return the first non-null value among the provided values (NULL, 76, 567). In this case, the first non-null value is 76. The result is aliased as value and inserted into the barStream.

Essentially, this query processes records in the fooStream and creates new records in the barStream with a constant value field set to 76.

Example 3

@info(name = 'query1')
INSERT INTO barStream
SELECT coalesce(NULL, NULL, NULL) AS value
FROM fooStream;

This query selects records from the fooStream and uses the coalesce function to return the first non-null value among the provided values (NULL, NULL, NULL). In this case, all values are null, so the coalesce function returns NULL. The result is aliased as value and inserted into the barStream.

Essentially, this query processes records in the fooStream and creates new records in the barStream with a constant value field set to NULL.