Collections and Rendering

Constants can hold collections as well as scalar values. Use this page when a constant represents a reusable value list, native array, set, or structured object.

Lists

Square brackets declare an ordered list. Lists preserve authored order and allow duplicates:

CONSTANT (
  name supported_countries,
  value ["GB", "FR", "HK"],
);

Sets

Curly braces declare a set. Sets reject duplicate typed values and use a stable order when SQLBuild renders or fingerprints them:

CONSTANT (
  name unique_countries,
  value {"GB", "FR", "HK"},
);

{true, 1} is valid because a boolean and an integer are different logical types. {"GB", "FR", "GB"} fails compilation instead of silently discarding the duplicate.

Objects

Parenthesized key-value entries declare a string-keyed object. Values may be scalars, lists, sets, or other objects:

CONSTANT (
  name country_rules,
  value (
    GB (
      label "Great Britain",
      threshold 2.47,
      enabled true,
      regions ["ENG", "SCT", "WLS"],
    ),
    FR (
      label "France",
      threshold 2.5,
      enabled true,
      regions ["IDF", "NAQ"],
    ),
  ),
);

Object keys must be unique. Objects are logical JSON values rather than portable homogeneous SQL maps or structs.

Collection rules

Lists and sets must be non-empty and have one compatible element type. Nullable elements do not determine the type, so [1, null, 2] is valid, while these declarations fail:

CONSTANT (name empty_values, value []);          -- no element type
CONSTANT (name unknown_values, value [null]);    -- no non-null element type
CONSTANT (name mixed_values, value [1, "two"]); -- incompatible types

Objects may contain different value types because each key is checked independently. SQLBuild also applies nesting-depth, element-count, and rendered-size safety limits.

Value-list rendering

Lists and sets render as a parenthesized value list by default. This is designed for IN:

WHERE country_code IN @const("supported_countries")

WHERE country_code IN ('GB', 'FR', 'HK')

Every element is escaped by the active adapter.

A value-list constant is intended for a value-list position such as IN (...). It is not a portable standalone projection. Use native-array rendering when the constant must be an array expression.

Native-array rendering

Set render_as array to request an adapter-native array:

CONSTANT (
  name supported_countries_array,
  value ["GB", "FR", "HK"],
  render_as array,
);

SQLBuild does not rewrite array membership operations. Use the operators and functions provided by your adapter. Sets support the same value_list and array modes as lists. Scalar and object constants reject render_as because those rendering modes do not apply to them.

Adapter	Native array expression	Object/JSON expression
DuckDB	`['GB', 'FR', 'HK']`	`json('{"GB":"Great Britain"}')`
MotherDuck	`['GB', 'FR', 'HK']`	`json('{"GB":"Great Britain"}')`
Snowflake	`ARRAY_CONSTRUCT('GB', 'FR', 'HK')`	`PARSE_JSON('{"GB":"Great Britain"}')`
BigQuery	`['GB', 'FR', 'HK']`	`JSON '{"GB":"Great Britain"}'`
Databricks	`array('GB', 'FR', 'HK')`	`parse_json('{"GB":"Great Britain"}')`
PostgreSQL	`ARRAY['GB', 'FR', 'HK']`	`'{"GB":"Great Britain"}'::JSONB`
SQL Server	Unsupported	`JSON_QUERY(N'{"GB":"Great Britain"}')`

BigQuery does not support arrays containing arrays. SQL Server has no native array representation. Unsupported requests fail compilation rather than silently changing representation.

Project default

Set the default for list and set constants in sqlbuild_project.toml:

[constants]
collection_rendering = "array"

SQLBuild chooses the rendering mode in this order:

The declaration’s render_as field
Project [constants].collection_rendering
The value_list default

One constant has one representation throughout a compilation. Changing its value or rendering mode changes the identity of SQL that uses it.

Stable values

List order and duplicates are preserved.
Set order is ignored; membership is stored in a stable order.
Object key order is ignored; keys are stored in a stable order.
Changing set membership or object values changes dependent query identity.

Getting Started

dbt Compatibility

Concepts

Advanced Concepts

Python Nodes

Virtual Environments (Alpha)

Integrations

CLI Reference

Collections and Rendering

Lists

Sets

Objects

Collection rules

Value-list rendering

Native-array rendering

Project default

Stable values

​Lists

​Sets

​Objects

​Collection rules

​Value-list rendering

​Native-array rendering

​Project default

​Stable values

Lists

Sets

Objects

Collection rules

Value-list rendering

Native-array rendering

Project default

Stable values