Converts a BSON document to a PSQL array.




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

Return Type

A PSQL array.


  • 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.


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.

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.

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":[]} |
| {}                 |

Last modified: May 8, 2024

Was this article helpful?