BSON_ EXTRACT_ ALL_ BSON
On this page
Extracts and returns all the values from a BSON document or array, optionally at the specified path.
If the specified path matches an array, BSON_
recursively extracts and aggregates values from each element at the specified path.
Syntax
BSON_EXTRACT_ALL_BSON(<input> [, path_key_1, path_key_2,.., path_key_n])
Arguments
-
input
: A valid BSON document or an expression that evaluates to a valid BSON document. -
path
: A comma-separated list of dictionary keys or array positions (zero-based index).
Return Type
A BSON
value.
Remarks
-
If the
path_
argument is not specified, the top-level document or value is returned.key -
If the specified
path_
does not exist, returnskey NULL
.
Examples
Note: The following examples explicitly cast string to BSON for clarity.
-
Returns values for all the matching keys in the BSON document:
SELECTBSON_EXTRACT_ALL_BSON('{"c":[{"a":[{"b":1},{"b":2},{"b":3},{"b":4}]}]}':>BSON, 'c', 'a', 'b'):>JSON AS Result;+-------------+ | Result | +-------------+ | [[1,2,3,4]] | +-------------+
SELECTBSON_EXTRACT_ALL_BSON('{"a":[{"b":[1,2]},{"b":[3,4]},{"b":[5,6]},{"b":[7,8]}]}':>BSON, 'a', 'b'):>JSON AS Result;+---------------------------+ | Result | +---------------------------+ | [[1,2],[3,4],[5,6],[7,8]] | +---------------------------+
-
Returns the top-level document because the path is not specified:
SELECTBSON_EXTRACT_ALL_BSON('{"c":[{"a":[{"b":1},{"b":2},{"b":3},{"b":4}]}]}':>BSON):>JSON AS Result;+-------------------------------------------------+ | Result | +-------------------------------------------------+ | {"c":[{"a":[{"b":1},{"b":2},{"b":3},{"b":4}]}]} | +-------------------------------------------------+
-
Returns
NULL
because the specified path does not exist in the BSON document:SELECTBSON_EXTRACT_ALL_BSON('{"c":[{"a":[{"b":1},{"b":2},{"b":3},{"b":4}]}]}':>BSON, 'c', 'd'):>JSON AS Result;+--------+ | Result | +--------+ | NULL | +--------+
Last modified: July 10, 2025