BSON_
On this page
Extracts the string value for the specified key from the BSON data, converts it to a SingleStore string type, and returns the value.
Syntax
BSON_EXTRACT_STRING(<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 string.
Remarks
-
The BSON string types are returned as is.
-
The default collation for this function is
utf8mb4_.bin -
Returns
NULLif:-
The extracted value is an invalid BSON string, a BSON type other than string, or
NULL. -
The specified path does not exist.
-
Examples
Note: The following examples explicitly cast string to BSON for clarity.
The following example extracts a string:
SELECT BSON_EXTRACT_STRING('{"a":{"b":"A valid string"}}':>BSON, 'a', 'b') AS Result;
+----------------+
| Result |
+----------------+
| A valid string |
+----------------+The following examples return NULL because of invalid input types:
SELECT BSON_EXTRACT_STRING('{"a":{"b":{"$numberDecimal":"12.5"}}}':>BSON, 'a', 'b') AS Result;
+--------+
| Result |
+--------+
| NULL |
+--------+SELECT BSON_EXTRACT_STRING('{"a":{"b":{"$date":"2023-03-14T09:48:10.720Z"}}}':>BSON, 'a', 'b') AS Result;
+--------+
| Result |
+--------+
| NULL |
+--------+SELECT BSON_EXTRACT_STRING('{"a":{"b":{"$oid":"641044fd4542e7dc53758df9"}}}':>BSON, 'a', 'b') AS Result;
+--------+
| Result |
+--------+
| NULL |
+--------+Last modified: April 2, 2024