1 of 8

Array

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

ELEMENT_OF

This page describes the ELEMENT_OF function in Lenses SQL.

ELEMENT_OF(array, index)

Return the element of array at index.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT ELEMENT_OF(products, 2) 
FROM orders-events
LIMIT 1;

Output:

{
  "value": {
    "ELEMENT_OF": {
      "product_id": "d3f085e0-c049-4e8b-9dd9-ea3ae124720b",
      "quantity": 1
    }
  }
}

FLATTEN

This page describes the FLATTEN function in Lenses SQL.

FLATTEN(array)

Flatten an array of arrays into an array.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    FLATTEN(_value), 
    _value 
FROM products-array-test-2
LIMIT 1;

Output:

{
  "value": {
    "FLATTEN": [
      "128279ca-5f8d-44ef-b340-b8054a6611ec",
      6,
      "5ae063ba-0c94-4b9c-ad40-2da08ad8bfd2",
      8,
      "41bb7268-fa7c-426f-b549-4bb215579280",
      9,
      "35df5368-8b77-4081-9aab-96bc8de16c23",
      10,
      "da6d69ce-bbd9-4e56-861d-5018f2d9b23a",
      10,
      "c9725591-e6b6-4928-a6e8-7e37707965ec",
      2,
      "ac821f76-2ff0-4d0c-9540-b775f0602332",
      3,
      "f1f98a2c-e66e-44f3-bba1-58169a425862",
      1,
      "78ea76f1-7381-4b38-a25f-ee487bae9749",
      10,
      "5b84f92d-d8b8-4532-b452-639ab44ae017",
      5
    ]
  }
}
{
  "value": [
    [
      "128279ca-5f8d-44ef-b340-b8054a6611ec",
      6
    ],
    [
      "5ae063ba-0c94-4b9c-ad40-2da08ad8bfd2",
      8
    ],
    [
      "41bb7268-fa7c-426f-b549-4bb215579280",
      9
    ],
    [
      "35df5368-8b77-4081-9aab-96bc8de16c23",
      10
    ],
    [
      "da6d69ce-bbd9-4e56-861d-5018f2d9b23a",
      10
    ],
    [
      "c9725591-e6b6-4928-a6e8-7e37707965ec",
      2
    ],
    [
      "ac821f76-2ff0-4d0c-9540-b775f0602332",
      3
    ],
    [
      "f1f98a2c-e66e-44f3-bba1-58169a425862",
      1
    ],
    [
      "78ea76f1-7381-4b38-a25f-ee487bae9749",
      10
    ],
    [
      "5b84f92d-d8b8-4532-b452-639ab44ae017",
      5
    ]
  ]
}

IN_ARRAY

This page describes the IN_ARRAY function in Lenses SQL.

IN_ARRAY(element, array)

Check if element is an element of array.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    IN_ARRAY('5ec69520-258f-4669-915f-9d842f96fa14', products), 
    products 
FROM orders-test-4
LIMIT 10;

Output:

{
  "value": {
    "IN_ARRAY": true,
    "products": [
      "5ec69520-258f-4669-915f-9d842f96fa14",
      "1946c4ac-ea7a-48c5-870c-1800c3982a14"
    ]
  }
}
{
  "value": {
    "IN_ARRAY": false,
    "products": [
      "49c537d5-078f-4bf8-a74d-49aa10e39c31",
      "c1eb9291-f292-4079-8957-88cce18c2739"
    ]
  }
}

REPEAT

This page describes the REPEAT function in Lenses SQL.

REPEAT(element, n)

Build an array repeating element n times.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    REPEAT(products, 5), 
    products 
FROM orders-topic
LIMIT 1;

Output:

{
  "value": {
    "REPEAT": [
      [
        {
          "product_id": "4e774082-0c03-4751-b4dc-c3799da54f48",
          "quantity": 3
        }
      ],
      [
        {
          "product_id": "4e774082-0c03-4751-b4dc-c3799da54f48",
          "quantity": 3
        }
      ],
      [
        {
          "product_id": "4e774082-0c03-4751-b4dc-c3799da54f48",
          "quantity": 3
        }
      ],
      [
        {
          "product_id": "4e774082-0c03-4751-b4dc-c3799da54f48",
          "quantity": 3
        }
      ],
      [
        {
          "product_id": "4e774082-0c03-4751-b4dc-c3799da54f48",
          "quantity": 3
        }
      ]
    ],
    "products": [
      {
        "product_id": "4e774082-0c03-4751-b4dc-c3799da54f48",
        "quantity": 3
      }
    ]
  }
}

SIZEOF

This page describes the SIZEOF function in Lenses SQL.

SIZEOF(expr)

Returns the number of elements in an array.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
  SIZEOF(products) AS qty_products_array, 
  products 
FROM orders-events
LIMIT 5;

Output:

{
  "value": {
    "qty_products_array": 1,
    "products": [
      {
        "product_id": "0e719f35-e7fb-4717-9151-b4f3cd2017a2",
        "quantity": 5
      }
    ]
  }
}
{
  "value": {
    "qty_products_array": 5,
    "products": [
      {
        "product_id": "0847e631-150b-45e3-a2cd-c31b11afef3c",
        "quantity": 8
      },
      {
        "product_id": "707b30ad-a506-4583-acc8-95856d3bb88e",
        "quantity": 1
      },
      {
        "product_id": "efdfe7e2-fc01-4018-8d7f-ac0a130d0292",
        "quantity": 6
      },
      {
        "product_id": "2f9c5899-3df4-4090-ba8c-3b97f34e3afe",
        "quantity": 8
      },
      {
        "product_id": "e1c71c52-fa6f-47d7-a488-1eadc8a04e5d",
        "quantity": 1
      }
    ]
  }
}
{
  "value": {
    "qty_products_array": 2,
    "products": [
      {
        "product_id": "01fb793f-baad-44b2-8a52-55cdfbe062f1",
        "quantity": 8
      },
      {
        "product_id": "29a2914c-7f81-4c06-b3a4-a42124d5fdf3",
        "quantity": 10
      }
    ]
  }
}
{
  "value": {
    "qty_products_array": 5,
    "products": [
      {
        "product_id": "77ea0afb-3184-4a1a-bc08-d1cfdab445ad",
        "quantity": 1
      },
      {
        "product_id": "798e2412-ebc0-4549-a935-632f1dccc48c",
        "quantity": 7
      },
      {
        "product_id": "768e6a17-b5d9-4488-b4b4-62e54ea9e818",
        "quantity": 8
      },
      {
        "product_id": "3876af98-2b6c-4690-a3c2-ab7a9c4e2565",
        "quantity": 3
      },
      {
        "product_id": "23c12a75-7a16-46ef-a253-21aae8b705ba",
        "quantity": 2
      }
    ]
  }
}
{
  "value": {
    "qty_products_array": 4,
    "products": [
      {
        "product_id": "acd8758a-36b0-429c-9246-3e5cf9dc0545",
        "quantity": 6
      },
      {
        "product_id": "7262d1ce-f467-47f5-b835-061eb82593ca",
        "quantity": 1
      },
      {
        "product_id": "0751080a-d707-4c76-90fd-17b8724f37a2",
        "quantity": 3
      },
      {
        "product_id": "96630315-46d2-489a-9e2c-bb5ece7fab38",
        "quantity": 4
      }
    ]
  }
}

ZIP_ALL

This page describes the ZIP_ALL function in Lenses SQL.

ZIP_ALL(array1, field1, array2, field2, …)

Zip two or more arrays into a single one, returning nulls when an array is not long enough. Example: ZIP_ALL([1, 2], 'x', [3, 4, 5], 'y') will be evaluated to [{ x: 1, y: 3 }, { x: 2, y: 4 }, { x: null, y: 5 }]

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    ZIP_ALL([1, 2], 'x', [3, 4, 5], 'y') 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "ZIP_ALL": [
      {
        "x": 1,
        "y": 3
      },
      {
        "x": 2,
        "y": 4
      },
      {
        "x": null,
        "y": 5
      }
    ]
  }
}

ZIP

This page describes the ZIP function in Lenses SQL.

ZIP(array1, field1, array2, field2, …)

Zip two or more arrays into a single one. Example: ZIP([1, 2], 'x', [3, 4, 5], 'y') will be evaluated to [{ x: 1, y: 3 }, { x: 2, y: 4 }]

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    ZIP([1, 2], 'x', [3, 4, 5], 'y') 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "ZIP": [
      {
        "x": 1,
        "y": 3
      },
      {
        "x": 2,
        "y": 4
      }
    ]
  }
}