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.

Return the element of array at index.

Available in:

Processors

SQL Studio

Sample code:

Output:

FLATTEN

This page describes the FLATTEN function in Lenses SQL.

Flatten an array of arrays into an array.

Available in:

Processors

SQL Studio

Sample code:

Output:

IN_ARRAY

This page describes the IN_ARRAY function in Lenses SQL.

Check if element is an element of array.

Available in:

Processors

SQL Studio

Sample code:

Output:

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.

Returns the number of elements in an array.

Available in:

Processors

SQL Studio

Sample code:

Output:

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
      }
    ]
  }
}

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
      }
    ]
  }
}

{
  "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, …)

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
      }
    ]
  }
}