Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
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