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_ 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