Arrays and Maps

Some SQL dialects have special syntax for creating and manipulating Array and Map types.

Literals

Array literals [1, "two", 3]. Supported by:

• BigQuery.
• DuckDB
• N1QL

Array literals ARRAY[1, 2, 3]. Supported by:

• BigQuery.
• DB2.
• DB2i.
• DuckDB
• PostgreSQL.
• Redshift
• Trino

Map literals in JSON style {"foo": 1, "bar": "John"}. Supported by:

• N1QL.
• DuckDB: keys can be key, "key", 'key'.

Accessors

Array and Map access using square brackets like arr[1] and map['key'].

Supported by:

• DB2
• DB2i
• DuckDB¹
• Hive
• Spark
• N1QL
• PostgreSQL
• Snowflake²
• Trino
• Redshift

Array subscript operator arr[OFFSET(5)]. Supported by:

• BigQuery

Array slice operator arr[a:b]. Supported by:

• DuckDB

Notes:

1. DuckDB also supports a dot syntax to access values of structs.
2. Snowflake also supports a dot syntax to access the values of keys. The first key has to be extracted with : after that, . can be used to access deeper nested values.