BSON_ARRAY_CONTAINS_BSON

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.

Syntax

BSON_ARRAY_CONTAINS_BSON(<array>, <value>)

Arguments

<array>: A BSON array or an expression that evaluates to a valid BSON array.
<value>: The value to find in the array.

Return Type

SingleStore TINYINT type.

Remarks

If the specified <array> argument is not a valid BSON array, returns NULL.
If the specified <array> argument is SQL NULL, returns 0.
If the specified <value> is present in the <array>, returns 1, otherwise 0.

Examples

Note: The following examples explicitly cast string to BSON for clarity.

SQL

SELECT BSON_ARRAY_CONTAINS_BSON('[true,"abc",123]':>BSON, '123':>BSON) AS Result;

+--------+
| Result |
+--------+
|      1 |
+--------+

SQL

SELECT BSON_ARRAY_CONTAINS_BSON('[{"a":1,"b":2},"abc",123]':>BSON, '{"b":1,"a":2}':>BSON) AS Result;

+--------+
| Result |
+--------+
|      0 |
+--------+

SQL

SELECT BSON_ARRAY_CONTAINS_BSON('{"a":12,"b":23}':>BSON, '123':>BSON) AS Result;

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