BSON_UNWIND

Syntax

BSON_UNWIND(<input>)

Argument

<input>: A valid BSON type or array, or an expression that evaluates to a valid BSON type or array.

Return Type

A PSQL array.

Remarks

If the input is a valid non-empty BSON array, a PSQL array is returned.
If the input is a BSON type other than the array, it is mapped to the PSQL array as a single element.
BSON_UNWIND() returns NULL if the input is a SQL NULL, BSON NULL, BSON undefined, or an empty BSON array.

Examples

Note: The following examples explicitly cast string to BSON for clarity. Similarly, the output is cast to JSON. The TABLE() command is used to convert the PSQL array to a set of rows.

The following example adds the BSON input as a single element in the array because the input is not a BSON array.

SQL

SELECT table_col:>JSON AS Result
FROM TABLE(BSON_UNWIND('{"field1":"string value","field2":[12,3,1331,"arr"],"field3":true,"field4":null}':>BSON));

+----------------------------------------------------------------------------------+
| Result                                                                           |
+----------------------------------------------------------------------------------+
| {"field1":"string value","field2":[12,3,1331,"arr"],"field3":true,"field4":null} |
+----------------------------------------------------------------------------------+

The following example converts a BSON array to a PSQL array and returns the result as a set of rows, where each row represents an element in the array.

SQL

SELECT table_col:>JSON AS Result FROM TABLE(BSON_UNWIND('[1,true,null,"a string",2,3,4,5,{"a":"abc","b":[]},{}]':>BSON));

+--------------------+
| Result             |
+--------------------+
| 1                  |
| true               |
| null               |
| "a string"         |
| 2                  |
| 3                  |
| 4                  |
| 5                  |
| {"a":"abc","b":[]} |
| {}                 |
+--------------------+