In DuckDB, the bool_or() aggregate function returns true if any input value is true, otherwise it returns false.

Here are some basic examples that demonstrate how it works.

Syntax

The syntax goes like this:

bool_or(arg)

Where arg is the target boolean column or expression that the function operates on.

Basic Example

Suppose we create the following table:

-- Create a simple table
CREATE TABLE simple_bool (value BOOLEAN);

-- Insert some boolean values
INSERT INTO simple_bool VALUES (TRUE), (FALSE), (TRUE);

Now let’s use bool_or() to check if any value is true:

SELECT 
    bool_or(value) AS any_true 
FROM simple_bool;

Output:

+----------+
| any_true |
+----------+
| true     |
+----------+

It returned true because at least one value is true (in this case there are two true values in the table). Even though one of the values is false, the table contains at least one value that’s true, and that’s all that’s needed in order to return true.

Let’s update the table so that all values are false and run the query again:

-- Set all values to true
UPDATE simple_bool SET value = FALSE;

-- Run the query again
SELECT 
    bool_or(value) AS any_true 
FROM simple_bool;

Output:

+----------+
| any_true |
+----------+
| false    |
+----------+

As expected, it returned false.

Grouped Data

We can use the GROUP BY clause and apply bool_or() to each group.

Let’s create another table:

-- Create a table with categories and boolean values
CREATE TABLE grouped_bool (category TEXT, value BOOLEAN);

-- Insert values into the table
INSERT INTO grouped_bool VALUES
('A', TRUE), 
('A', FALSE), 
('B', FALSE), 
('B', FALSE), 
('C', TRUE);

Now let’s use bool_or() against each group:

SELECT 
    category, 
    bool_or(value) AS any_true_per_group
FROM grouped_bool
GROUP BY category;

Result:

+----------+--------------------+
| category | any_true_per_group |
+----------+--------------------+
| A        | true               |
| B        | false              |
| C        | true               |
+----------+--------------------+

This query groups the data by category and checks if any of the boolean values in each group are true.

• For category A, the result is true, because one of its values is true.
• But for category B, it is false because none of its values are true (they’re both false).
• As for category C, its sole value is true, and therefore it returns true.

Combining with Conditions

Let’s create another table:

-- Create a more complex table with conditions
CREATE TABLE conditional_bool (id INTEGER, status BOOLEAN, active BOOLEAN);

-- Insert values into the table
INSERT INTO conditional_bool VALUES
(1, TRUE, TRUE),
(2, TRUE, TRUE),
(3, FALSE, TRUE),
(4, TRUE, FALSE);

Now let’s use bool_or() in combination with a conditional query to filter rows:

SELECT bool_or(status) AS any_active_true
FROM conditional_bool
WHERE active = TRUE;

Result:

+-----------------+
| any_active_true |
+-----------------+
| true            |
+-----------------+

This query filters the rows where active is true and then applies bool_or() to check if any of the status values in these filtered rows are true. The result is true because some rows are true.

Let’s update the first two rows to FALSE and run the query again:

UPDATE conditional_bool SET status = FALSE WHERE id IN (1, 2);

SELECT bool_or(status) AS any_active_true
FROM conditional_bool
WHERE active = TRUE;

Result:

+-----------------+
| any_active_true |
+-----------------+
| false           |
+-----------------+

This time bool_or() returned false as expected.