Skip to main content
Bitmaps can be constructed using two different methods:
  1. Aggregation function: Use the groupBitmap function with the -State suffix to aggregate values into a bitmap.
  2. Array conversion: Convert an existing Array object into a bitmap data structure.
These methods allow for efficient creation and manipulation of bitmap data structures.

​
ClickHouse function reference

​
bitmapBuild

Builds a bitmap from an unsigned integer array. Syntax: 
bitmapBuild(array)
Arguments:
  • array - Unsigned integer array.
Returns: A bitmap object constructed from the input array. Example: 
SELECT
  bitmapBuild([1, 2, 3, 4, 5]) AS bitmap,
  toTypeName(bitmap) AS type
Result: 
| bitmap | type                                        |
|--------|---------------------------------------------|
|        | AggregateFunction(groupBitmap, UInt8)       |
In this example, bitmapBuild constructs a bitmap from the array of IDs [1, 2, 3, 4, 5]. The resulting bitmap is of type AggregateFunction(groupBitmap, UInt8).

​
bitmapToArray

Converts a bitmap object to an array of unsigned integers. Syntax: 
bitmapToArray(bitmap)
Arguments:
  • bitmap - A bitmap object.
Returns: An array of unsigned integers containing the set bits from the bitmap. Example: 
SELECT bitmapToArray(bitmapBuild([1, 3, 5, 7, 9])) AS taco_ids;
Result: 
| taco_ids        |
|-----------------|
| [1, 3, 5, 7, 9] |
In this example, bitmapToArray converts a bitmap containing the taco IDs 1, 3, 5, 7, and 9 back into an array of integers.
The returned array will be sorted in ascending order of the set bits, regardless of the order they were originally added to the bitmap.

​
bitmapSubsetInRange

Returns a subset of a bitmap containing only the bits within a specified range. Syntax: 
bitmapSubsetInRange(bitmap, range_start, range_end)
Arguments:
  • bitmap (Bitmap object): The input bitmap.
  • range_start (UInt32): The start of the range (inclusive).
  • range_end (UInt32): The end of the range (exclusive).
Returns: A new bitmap containing only the bits from the input bitmap that fall within the specified range. Example: 
SELECT
  bitmapToArray(
    bitmapSubsetInRange(
      bitmapBuild([1,2,3,4,5,6,7,8,9,10,11,12]),
      toUInt32(3),
      toUInt32(8)
    )
  ) AS taco_subset;
Result: 
| taco_subset |
|-------------|
| [3,4,5,6,7] |
In this example, bitmapSubsetInRange extracts a subset of the bitmap containing only the values between 3 (inclusive) and 8 (exclusive), which represents taco IDs 3 through 7.
This function is useful for efficiently extracting a range of values from a bitmap without needing to materialize the entire bitmap as an array.

​
bitmapSubsetLimit

Returns a subset of a bitmap with smallest bit value range_start and at most cardinality_limit elements. Syntax: 
bitmapSubsetLimit(bitmap, range_start, cardinality_limit)
Arguments:
  • bitmap (Bitmap) - The input bitmap.
  • range_start (UInt32) - The start of the range (inclusive).
  • cardinality_limit (UInt32) - The maximum cardinality of the subset.
Returns: A bitmap subset with at most cardinality_limit elements, starting from range_start. Example: 
SELECT bitmapToArray(bitmapSubsetLimit(
  bitmapBuild([1,2,3,4,5,6,7,8,9,10,11,12]),
  toUInt32(5),
  toUInt32(3)
)) AS taco_subset;
Result: 
| taco_subset |
|-------------|
| [5,6,7]     |
In this example, bitmapSubsetLimit returns a subset of the bitmap starting from value 5, with a maximum of 3 elements. The result is then converted to an array for display.

​
subBitmap

Returns a subset of the bitmap, starting from position offset. The maximum cardinality of the returned bitmap is cardinality_limit. Syntax: 
subBitmap(bitmap, offset, cardinality_limit)
Arguments:
  • bitmap (Bitmap) - The input bitmap.
  • offset (UInt32) - The position of the first element of the subset.
  • cardinality_limit (UInt32) - The maximum number of elements in the subset.
Returns: A subset of the input bitmap. Example: 
SELECT bitmapToArray(subBitmap(bitmapBuild([1,2,3,4,5,6,7,8,9,10]), 3, 4)) AS subset;
Result: 
| subset    |
|-----------|
| [4,5,6,7] |
In this example, subBitmap returns a subset of the bitmap starting from the 4th element (offset 3) and including up to 4 elements. The result is then converted to an array for display.

​
bitmapContains

Checks whether a bitmap contains a specific element. Syntax: 
bitmapContains(bitmap, needle)
Arguments:
  • bitmap (Bitmap): The input bitmap.
  • needle (UInt32): The element to search for in the bitmap.
Returns:
  • 1 if the bitmap contains the needle element. [UInt8]
  • 0 if the bitmap does not contain the needle element. [UInt8]
Example: 
SELECT
  taco_id,
  toppings_bitmap,
  bitmapContains(toppings_bitmap, 3) AS has_salsa
FROM taco_orders;
Result: 
| taco_id | toppings_bitmap | has_salsa |
|---------|-----------------|-----------|
| 1       | [1,2,3,4]       | 1         |
| 2       | [1,2,4]         | 0         |
| 3       | [2,3,5]         | 1         |
In this example, bitmapContains checks if each taco’s toppings bitmap contains the topping with ID 3 (assuming 3 represents salsa). The result is 1 if salsa is present and 0 if it’s not.

​
bitmapHasAny

Checks whether two bitmaps intersect (have any elements in common). Syntax: 
bitmapHasAny(bitmap1, bitmap2)
Arguments:
  • bitmap1 (Bitmap): The first bitmap object
  • bitmap2 (Bitmap): The second bitmap object
Returns:
  • 1 if the bitmaps intersect (have at least one element in common)
  • 0 if the bitmaps do not intersect
Example: 
SELECT
  bitmapHasAny(
    bitmapBuild([1, 2, 3, 4, 5]),
    bitmapBuild([3, 4, 5, 6, 7])
  ) AS has_intersection,
  bitmapHasAny(
    bitmapBuild([1, 2, 3]),
    bitmapBuild([4, 5, 6])
  ) AS no_intersection
FROM taco_orders;
Result: 
| has_intersection  | no_intersection |
|-------------------|-----------------|
| 1                 | 0               |
If bitmap2 contains exactly one element, consider using bitmapContains instead as it is more efficient for that case.

​
bitmapHasAll

Returns 1 if the first bitmap contains all elements of the second bitmap, otherwise 0. If the second bitmap is empty, returns 1. Syntax: 
bitmapHasAll(bitmap1, bitmap2)
Arguments:
  • bitmap1 (Bitmap): The first bitmap object to check.
  • bitmap2 (Bitmap): The second bitmap object to check against.
Returns:
  • 1 if bitmap1 contains all elements in bitmap2
  • 0 otherwise
  • 1 if bitmap2 is empty
Example: 
SELECT
  bitmapHasAll(
    bitmapBuild([1,2,3,4,5]),
    bitmapBuild([3,4,5])
  ) AS all_tacos_available,
  bitmapHasAll(
    bitmapBuild([1,2,3]),
    bitmapBuild([3,4,5])
  ) AS some_tacos_missing
Result: 
| all_tacos_available | some_tacos_missing  |
|---------------------|---------------------|
| 1                   | 0                   |
In this example:
  • The first bitmapHasAll returns 1 because [1,2,3,4,5] contains all elements of [3,4,5].
  • The second bitmapHasAll returns 0 because [1,2,3] does not contain all elements of [3,4,5].

​
bitmapCardinality

Returns the cardinality (number of set bits) of a bitmap. Syntax: 
bitmapCardinality(bitmap)
Arguments:
  • bitmap (Bitmap): A bitmap object.
Returns: The number of set bits in the bitmap. [UInt64] Example: 
SELECT bitmapCardinality(bitmapBuild([1, 2, 3, 4, 5])) AS count;
Result: 
| count |
|-------|
| 5     |
In this example, bitmapCardinality counts the number of elements in the bitmap created from the array [1, 2, 3, 4, 5].

​
bitmapMin

Computes the smallest bit set in a bitmap, or UINT32_MAX if the bitmap is empty. Syntax: 
bitmapMin(bitmap)
Arguments:
  • bitmap (Bitmap): Bitmap object.
Returns: The smallest bit set in the bitmap, or UINT32_MAX (4294967295) if the bitmap is empty. [UInt32] Example: 
SELECT bitmapMin(bitmapBuild([3, 5, 7, 9])) AS min_taco_id;
Result: 
| min_taco_id |
|-------------|
| 3           |
If the bitmap is empty, the function returns UINT32_MAX (4294967295).

​
bitmapMax

Computes the greatest bit set in a bitmap, or 0 if the bitmap is empty. Syntax: 
bitmapMax(bitmap)
Arguments:
  • bitmap (Bitmap): Bitmap object.
Returns: The greatest bit set in the bitmap, or 0 if empty. [UInt32] Example: 
SELECT bitmapMax(bitmapBuild([1, 2, 3, 4, 5])) AS max_taco_id;
Result: 
| max_taco_id |
|-------------|
| 5           |
In this example, bitmapMax finds the largest taco ID (5) from a bitmap of taco IDs.

​
bitmapTransform

Replaces bits in a bitmap based on provided mapping arrays. Syntax: 
bitmapTransform(bitmap, from_array, to_array)
Arguments:
  • bitmap (Bitmap): The input bitmap to transform.
  • from_array (Array(UInt32)): Array of bit values to replace.
  • to_array (Array(UInt32)): Array of new bit values to set.
Returns: A new bitmap with the specified bits replaced. [Bitmap]
  • The from_array and to_array must have the same length.
  • For each index i, if the bitmap contains from_array[i], it is replaced with to_array[i].
  • The result depends on the order of elements in from_array and to_array.
Example: 
SELECT
  bitmapToArray(
    bitmapTransform(
      bitmapBuild([1, 2, 3, 4, 5]),
      cast([2, 4] as Array(UInt32)),
      cast([10, 20] as Array(UInt32))
    )
  ) AS transformed_bitmap;
Result: 
| transformed_bitmap |
|--------------------|
| [1,3,5,10,20]      |
In this example, the bitmap [1,2,3,4,5] is transformed by replacing 2 with 10 and 4 with 20, resulting in [1,3,5,10,20].

​
bitmapAnd

Computes the logical AND (intersection) of two bitmaps. Syntax: 
bitmapAnd(bitmap1, bitmap2)
Arguments:
  • bitmap1 (Bitmap): The first bitmap object
  • bitmap2 (Bitmap): The second bitmap object
Returns: A new bitmap object containing the intersection of the input bitmaps. [Bitmap] Example: 
SELECT
  bitmapToArray(
    bitmapAnd(
      bitmapBuild([1,2,3,4,5]),
      bitmapBuild([3,4,5,6,7])
    )
  ) AS intersection;
Result: 
| intersection |
|--------------|
| [3,4,5]      |

​
bitmapOr

Computes the logical OR (disjunction) of two bitmaps. Syntax: 
bitmapOr(bitmap1, bitmap2)
Arguments:
  • bitmap1 (Bitmap): The first bitmap object.
  • bitmap2 (Bitmap): The second bitmap object.
Returns: A new bitmap object representing the union of the input bitmaps. Example: 
SELECT
  bitmapToArray(
    bitmapOr(
      bitmapBuild([1,3,5,7,9]),
      bitmapBuild([2,4,6,8,10])
    )
  ) AS combined_taco_ids;
Result: 
| combined_taco_ids         |
|---------------------------|
| [1,2,3,4,5,6,7,8,9,10]    |
In this example, bitmapOr combines two bitmaps representing odd and even taco IDs, resulting in a bitmap containing all IDs from 1 to 10. The bitmapToArray function is used to convert the result back to an array for display.

​
bitmapXor

Performs a bitwise XOR operation on two bitmaps. Syntax: 
bitmapXor(bitmap1, bitmap2)
Arguments:
  • bitmap1 (Bitmap): The first bitmap object.
  • bitmap2 (Bitmap): The second bitmap object.
Returns: A new bitmap object containing the result of the XOR operation. [Bitmap] Example: 
SELECT
  bitmapToArray(
    bitmapXor(
      bitmapBuild([1,2,3]),
      bitmapBuild([3,4,5])
    )
  ) AS xor_result;
Result: 
| xor_result |
|------------|
| [1,2,4,5]  |

​
bitmapAndnot

Computes the logical AND of the first bitmap with the negation of the second bitmap. Syntax: 
bitmapAndnot(bitmap1, bitmap2)
Arguments:
  • bitmap1 (Bitmap): The first bitmap operand.
  • bitmap2 (Bitmap): The second bitmap operand to be negated.
Returns: A new bitmap containing the result of bitmap1 AND (NOT bitmap2). [Bitmap] Example: 
SELECT bitmapToArray(bitmapAndnot(
  bitmapBuild([1,2,3,4,5]),
  bitmapBuild([3,4,5,6,7])
)) AS result;
Result: 
| result |
|--------|
| [1,2]  |
In this example, bitmapAndnot performs the following operation:
  1. Takes the first bitmap [1,2,3,4,5]
  2. Negates the second bitmap [3,4,5,6,7] to get [1,2]
  3. Performs a logical AND between [1,2,3,4,5] and [1,2]
  4. Returns the result [1,2]
This function is useful for finding elements that are in the first set but not in the second set.

​
bitmapAndCardinality

Returns the cardinality of the logical AND (conjunction) of two bitmaps. Syntax: 
bitmapAndCardinality(bitmap1, bitmap2)
Arguments:
  • bitmap1 (Bitmap): The first bitmap object
  • bitmap2 (Bitmap): The second bitmap object
Returns: The number of set bits in the result of ANDing the two input bitmaps. [UInt64] Example: 
SELECT bitmapAndCardinality(
  bitmapBuild([1, 2, 3, 4, 5]),
  bitmapBuild([3, 4, 5, 6, 7])
) AS common_taco_toppings;
Result: 
| common_taco_toppings |
|----------------------|
| 3                    |
In this example, bitmapAndCardinality calculates how many taco toppings are common between two sets of toppings represented as bitmaps. The result 3 indicates that there are 3 toppings (3, 4, and 5) present in both bitmaps.

​
bitmapOrCardinality

Returns the cardinality of the logical OR (disjunction) of two bitmaps. Syntax: 
bitmapOrCardinality(bitmap1, bitmap2)
Arguments:
  • bitmap1 (Bitmap): First bitmap object
  • bitmap2 (Bitmap): Second bitmap object
Returns: The number of set bits in the result of ORing the two input bitmaps. [UInt64] Example: 
SELECT bitmapOrCardinality(
  bitmapBuild([1, 2, 3]),
  bitmapBuild([3, 4, 5])
) AS taco_combinations;
Result: 
| taco_combinations  |
|--------------------|
| 5                  |
In this example, bitmapOrCardinality calculates how many unique elements are available when combining two sets. The first bitmap represents [1, 2, 3] and the second represents [3, 4, 5]. The OR operation results in [1, 2, 3, 4, 5], and the cardinality (count of unique elements) is 5.

​
bitmapXorCardinality

Returns the cardinality of the XOR (exclusive or) of two bitmaps. Syntax: 
bitmapXorCardinality(bitmap1, bitmap2)
Arguments:
  • bitmap1 (Bitmap): The first bitmap object
  • bitmap2 (Bitmap): The second bitmap object
Returns: The cardinality (number of set bits) of the XOR of the two input bitmaps. [UInt64] Example: 
SELECT bitmapXorCardinality(
  bitmapBuild([1,2,3,4,5]),
  bitmapBuild([3,4,5,6,7])
) AS xor_cardinality;
Result: 
| xor_cardinality |
|-----------------|
| 4               |
In this example, the XOR of the two bitmaps is [1,2,6,7], which has a cardinality of 4. This function efficiently computes the cardinality of the XOR without materializing the full result bitmap, which can be useful for large bitmaps.

​
bitmapAndnotCardinality

Returns the cardinality of the AND-NOT operation of two bitmaps. Syntax: 
bitmapAndnotCardinality(bitmap1, bitmap2)
Arguments:
  • bitmap1 (Bitmap): The first bitmap object.
  • bitmap2 (Bitmap): The second bitmap object.
Returns: The number of elements in bitmap1 that are not in bitmap2. [UInt64] Example: 
SELECT
  bitmapToArray(bitmapBuild([1, 2, 3, 4, 5])) AS bitmap1,
  bitmapToArray(bitmapBuild([3, 4, 5, 6, 7])) AS bitmap2,
  bitmapAndnotCardinality(
    bitmapBuild([1, 2, 3, 4, 5]),
    bitmapBuild([3, 4, 5, 6, 7])
  ) AS andnot_cardinality;
Result: 
| bitmap1        | bitmap2          | andnot_cardinality   |
|----------------|------------------|----------------------|
| [1, 2, 3, 4, 5] | [3, 4, 5, 6, 7] | 2                    |
In this example, bitmapAndnotCardinality calculates the number of elements in bitmap1 that are not in bitmap2. The result is 2, corresponding to the elements [1, 2] which are in bitmap1 but not in bitmap2.