Syntax

BSON_EXTRACT_ALL_BSON(<input> [, path_key_1, path_key_2,.., path_key_n])

Remarks

• If the path_key argument is not specified, the top-level document or value is returned.

• If the specified path_key does not exist, returns NULL.

Examples

Note: The following examples explicitly cast string to BSON for clarity. Similarly, the output is cast to JSON.

• Returns values for all the matching keys in the BSON document:

  SQL

  Copy

  SELECT 
  BSON_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]] |
  +-------------+

  SQL

  Copy

  SELECT 
  BSON_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:

  SQL

  Copy

  SELECT 
  BSON_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:

  SQL

  Copy

  SELECT 
  BSON_EXTRACT_ALL_BSON('{"c":[{"a":[{"b":1},{"b":2},{"b":3},{"b":4}]}]}':>BSON, 'c', 'd'):>JSON AS Result;

  +--------+
  | Result |
  +--------+
  | NULL   |
  +--------+