1 of 6

Nulls

This page describes how to use NULL functions in Lenses SQL Processors.

ISNULL

This page describes the ISNULL function in Lenses SQL.

ISNULL(expr)

Returns true if the input is null; false otherwise.

Available in:

Processors

SQL Studio

Sample code:

// Some code
USE `kafka`;
SELECT 
  ISNULL(modified_at) AS modified_at,
   modified_at AS current_field 
FROM users-events
LIMIT 5;

Output:

{
  "value": {
    "modified_at": true,
    "current_field": null
  }
}
{
  "value": {
    "modified_at": true,
    "current_field": null
  }
}
{
  "value": {
    "modified_at": false,
    "current_field": "2023-11-02T02:46:12.882863"
  }
}
{
  "value": {
    "modified_at": true,
    "current_field": null
  }
}
{
  "value": {
    "modified_at": true,
    "current_field": null
  }
}

ISNOTNULL

This page describes the ISNOTNULL function in Lenses SQL.

ISNOTNULL(expr)

Returns true if the input is not null; false otherwise.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
  ISNOTNULL(modified_at) AS modified_at, 
  modified_at AS current_field 
FROM users-events
LIMIT 5;

Output:

{
  "value": {
    "modified_at": false,
    "current_field": null
  }
}
{
  "value": {
    "modified_at": false,
    "current_field": null
  }
}
{
  "value": {
    "modified_at": true,
    "current_field": "2023-11-05T10:50:25.201315"
  }
}
{
  "value": {
    "modified_at": false,
    "current_field": null
  }
}
{
  "value": {
    "modified_at": false,
    "current_field": null
  }
}

COALESCE

This page describes the COALESCE function in Lenses SQL.

COALESCE(value, prevValue)

Returns the first non-null expression in the expression list.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    COALESCE(modified_at, "not updated") AS modified_at 
FROM users-events
WHERE modified_at IS NULL
LIMIT 1;

Output:

{
  "value": {
    "modified_at": "not updated"
  }
}

In case you want to know more about nullability scenarios, see here.

AS_NULLABLE

This page describes the AS_NULLABLE function in Lenses SQL.

AS_NULLABLE(expr)

Returns the provided value with its type changed from the original type to its nullable version.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT AS_NULLABLE(modified_at) AS null_value 
FROM users-events
LIMIT 100;

Output:

{
  "value": {
    "null_value": null
  }
}

In case you want to know more about nullability scenarios, see here.

AS_NON_NULLABLE

This page describes the AS_NON_NULLABLE function in Lenses SQL.

AS_NON_NULLABLE(expr)

Returns the provided value with its type changed from the original type to its non-nullable version.

Available in:

Processors

SQL Studio

using case:

USE `kafka`;
SELECT 
CASE
       WHEN modified_at IS NULL THEN 0
       ELSE AS_NON_NULLABLE(modified_at)
END AS null_value FROM users-events
LIMIT 5;

Output:

{
  "value": {
    "null_value": "2023-11-07T05:27:04.740600"
  }
}
{
  "value": {
    "null_value": "2023-11-04T07:46:57.045631"
  }
}
{
  "value": {
    "null_value": 0
  }
}
{
  "value": {
    "null_value": 0
  }
}
{
  "value": {
    "null_value": 0
  }
}

In case you want to know more about nullability scenarios, see here.

AS_NON_NULLABLE

This page describes the AS_NON_NULLABLE function in Lenses SQL.

AS_NON_NULLABLE(expr)

Returns the provided value with its type changed from the original type to its non-nullable version.

Available in:

Processors

SQL Studio

using case:

USE `kafka`;
SELECT 
CASE
       WHEN modified_at IS NULL THEN 0
       ELSE AS_NON_NULLABLE(modified_at)
END AS null_value FROM users-events
LIMIT 5;

Output:

{
  "value": {
    "null_value": "2023-11-07T05:27:04.740600"
  }
}
{
  "value": {
    "null_value": "2023-11-04T07:46:57.045631"
  }
}
{
  "value": {
    "null_value": 0
  }
}
{
  "value": {
    "null_value": 0
  }
}
{
  "value": {
    "null_value": 0
  }
}

In case you want to know more about nullability scenarios, see here.