BSON_
On this page
Applies a mask to a BSON document and returns the subset of the original document.
BSON_ returns all the fields in the BSON document that match the specified mask, all other fields are excluded.BSON_ 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_: A valid BSON document or an expression that evaluates to a valid BSON document.input> -
<mask>: A JSON input in string format that represents the mask.
Return Type
A BSON document.
Remarks
Returns NULL, if the <bson_ argument is NULL or not a valid BSON document.
Mask Behavior
BSON_
-
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_
-
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.
BSON_ 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_
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