# BSON\_INCLUDE\_MASK and BSON\_EXCLUDE\_MASK

Applies a mask to a BSON document and returns the subset of the original document.

`BSON_INCLUDE_MASK` returns all the fields in the BSON document that match the specified mask, all other fields are excluded. Conversely, `BSON_EXCLUDE_MASK` returns all the fields that do not match the specified mask, and all the fields that match the specified mask are excluded.

## Syntax

```
BSON_INCLUDE_MASK(<bson_input>,<mask>)

BSON_EXCLUDE_MASK(<bson_input>,<mask>)
```

## Arguments

* `<bson_input>`: A valid BSON document or an expression that evaluates to a valid BSON document.
* `<mask>`: A JSON input in string format that represents the mask.

## Return Type

A BSON document.

## Remarks

Returns `NULL`, if the `<bson_input>` argument is `NULL` or not a valid BSON document.

## Mask Behavior

## `BSON_INCLUDE_MASK`

* All fields in the document that do not match the mask are excluded from the result. For example, if the mask `{"a": 1}` is specified, only the field `"a"` from the input document is included.
* For nested fields, if the value is a BSON document, it is included and the remainder of the mask is applied to the document. Consider the following nested field `{"a": {"b": 1}}`,

  * If `"a"` is an array, it is included and the mask is applied to all the elements of the array.
  * If `"a"` is not an array, the mask only applies directly to `"a"`.

## `BSON_EXCLUDE_MASK`

* All fields in the document that match the mask are excluded from the result. For example, if the mask `{"a": 1}` is specified, the field `"a"` from the input document is excluded.
* For nested fields, the mask is only applied to the leaves (terminal nodes/values).

## Examples

**Note**: The following examples explicitly cast string to BSON for clarity. Similarly, the output is cast to JSON.

## `BSON_INCLUDE_MASK` Examples

```sql
SELECT BSON_INCLUDE_MASK('{"a":1,"b":[{"c":1,"d":1},{"c":2,"d":2}],"e":1}':>BSON,'{"a":1,"b":1}'):>JSON AS Result;

```

```output

+-------------------------------------------+
| Result                                    |
+-------------------------------------------+
| {"a":1,"b":[{"c":1,"d":1},{"c":2,"d":2}]} |
+-------------------------------------------+
```

```sql
SELECT BSON_INCLUDE_MASK('{"a":1,"b":[{"c":1,"d":1},{"c":2,"d":2}],"e":1}':>BSON,'{"a":1,"b":{"c":1}}'):>JSON AS Result;

```

```output

+-------------------------------+
| Result                        |
+-------------------------------+
| {"a":1,"b":[{"c":1},{"c":2}]} |
+-------------------------------+
```

```sql
SELECT '{"a":[{"x":"abc", "y":123}, {"x":"cde", "z":234}, {"y":true, "z":null}],"b":true}':>BSON INTO @bsonObj;

SELECT BSON_INCLUDE_MASK(@bsonObj, '{"a":{"x":1}}'):>JSON AS Result;

```

```output

+------------------------------------+
| Result                             |
+------------------------------------+
| {"a":[{"x":"abc"},{"x":"cde"},{}]} |
+------------------------------------+
```

The following example specifies an empty mask.

```sql
SELECT BSON_INCLUDE_MASK('{"a":1,"b":[{"c":1,"d":1},{"c":2,"d":2}],"e":1}':>BSON,'{}'):>JSON AS Result;

```

```output

+--------+
| Result |
+--------+
| {}     |
+--------+
```

## BSON\_EXCLUDE\_MASK Examples

```sql
SELECT '{"a":[{"x":"abc", "y":123}, {"x":"cde", "z":234}, {"y":true, "z":null}],"b":true}':>BSON INTO @bsonObj;

SELECT BSON_EXCLUDE_MASK(@bsonObj, '{"a":{"x":1}}'):>JSON AS Result;

```

```output

+----------------------------------------------------------+
| Result                                                   |
+----------------------------------------------------------+
| {"a":[{"y":123},{"z":234},{"y":true,"z":null}],"b":true} |
+----------------------------------------------------------+
```

```sql
SELECT BSON_EXCLUDE_MASK('{"a":1,"b":[{"c":1,"d":1},{"c":2,"d":2}],"e":1}':>BSON,'{"a":1,"b":1}'):>JSON AS Result;

```

```output

+---------+
| Result  |
+---------+
| {"e":1} |
+---------+
```

The following example specifies an empty mask.

```sql
SELECT BSON_EXCLUDE_MASK('{"a":1,"b":[{"c":1,"d":1},{"c":2,"d":2}],"e":1}':>BSON,'{}'):>JSON AS Result;

```

```output

+-------------------------------------------------+
| Result                                          |
+-------------------------------------------------+
| {"a":1,"b":[{"c":1,"d":1},{"c":2,"d":2}],"e":1} |
+-------------------------------------------------+
```

***

Modified at: May 8, 2024

Source: [/cloud/reference/sql-reference/bson-functions/bson-include-mask/](https://docs.singlestore.com/cloud/reference/sql-reference/bson-functions/bson-include-mask/)

(An index of the documentation is available at /llms.txt)