BSON_EXTRACT_BSON

Syntax

BSON_EXTRACT_BSON(<bson_input> [, path_key_1, path_key_2, ..., path_key_n])

Arguments

<bson_input>: A valid BSON object or an expression that evaluates to a valid BSON object.
<path_key>: (Optional) A comma-separated list of dictionary keys or array positions (zero-based index) that represent the path to the BSON object or the value to extract.

Return Type

A BSON value.

Remarks

If the <path_key> argument is not specified, the top-level document or value is returned.
If the input is a top-level BSON value, BSON_EXTRACT_BSON() returns the input value if the <path_key> argument is not specified. Otherwise, it returns NULL.
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.

The examples use the following data set:

SQL

CREATE DATABASE dbTest;
USE dbTest;

CREATE TABLE bsonExample(a BSON);
INSERT INTO bsonExample VALUES('{"doc":{"field1":42,"field2":true,"field3":"foobar","field4":[1,2,3],"child_doc":{"a":123}}}':>BSON);
INSERT INTO bsonExample VALUES('{"doc":{"field1":28,"field2":false,"field3":"barfoo","field4":[7,8,9],"child_doc":{"a":42}}}':>BSON);
INSERT INTO bsonExample VALUES('{"doc":{"field1":32,"field2":true,"field3":"hohoho","field4":[3,2,1],"child_doc":{"a":488}}}':>BSON);

The following example returns the top level document because the <path_key> is not specified:

SQL

SELECT BSON_EXTRACT_BSON(a):>JSON AS Result FROM bsonExample;

+----------------------------------------------------------------------------------------------+
| Result                                                                                       |
+----------------------------------------------------------------------------------------------+
| {"doc":{"child_doc":{"a":123},"field1":42,"field2":true,"field3":"foobar","field4":[1,2,3]}} |
| {"doc":{"child_doc":{"a":42},"field1":28,"field2":false,"field3":"barfoo","field4":[7,8,9]}} |
| {"doc":{"child_doc":{"a":488},"field1":32,"field2":true,"field3":"hohoho","field4":[3,2,1]}} |
+----------------------------------------------------------------------------------------------+

The following example returns the BSON document at the specified path:

SQL

SELECT BSON_EXTRACT_BSON(a, 'doc','child_doc'):>JSON AS Result FROM bsonExample;

+-----------+
| Result    |
+-----------+
| {"a":42}  |
| {"a":488} |
| {"a":123} |
+-----------+

The following example returns the BSON array at the specified path:

SQL

SELECT BSON_EXTRACT_BSON(a, 'doc', 'field4'):>JSON AS Result FROM bsonExample;

+---------+
| Result  |
+---------+
| [3,2,1] |
| [7,8,9] |
| [1,2,3] |
+---------+

The following example returns NULL because the specified <path_key> does not exist.

SQL

SELECT BSON_EXTRACT_BSON(a, 'doc', 'doc'):>JSON AS Result FROM bsonExample;

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