Sign inTry Free

BSON_INCLUDE_MASK and BSON_EXCLUDE_MASK

On this page

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

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;
+-------------------------------------------+
| Result                                    |
+-------------------------------------------+
| {"a":1,"b":[{"c":1,"d":1},{"c":2,"d":2}]} |
+-------------------------------------------+
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;
+-------------------------------+
| Result                        |
+-------------------------------+
| {"a":1,"b":[{"c":1},{"c":2}]} |
+-------------------------------+
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;
+------------------------------------+
| Result                             |
+------------------------------------+
| {"a":[{"x":"abc"},{"x":"cde"},{}]} |
+------------------------------------+

The following example specifies an empty mask.

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

BSON_EXCLUDE_MASK Examples

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;
+----------------------------------------------------------+
| Result                                                   |
+----------------------------------------------------------+
| {"a":[{"y":123},{"z":234},{"y":true,"z":null}],"b":true} |
+----------------------------------------------------------+
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;
+---------+
| Result  |
+---------+
| {"e":1} |
+---------+

The following example specifies an empty mask.

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

Last modified: May 8, 2024

Was this article helpful?

On this page

Was this article helpful?