> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sqlbuild.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Collections and Rendering

> Define list, set, and object constants and control their adapter-specific SQL 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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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`:

```sql theme={null}
WHERE country_code IN @const("supported_countries")
```

```sql theme={null}
WHERE country_code IN ('GB', 'FR', 'HK')
```

Every element is escaped by the active adapter.

<Warning>
  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.
</Warning>

## Native-array rendering

Set `render_as array` to request an adapter-native array:

```sql theme={null}
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`:

```toml theme={null}
[constants]
collection_rendering = "array"
```

SQLBuild chooses the rendering mode in this order:

1. The declaration's `render_as` field
2. Project `[constants].collection_rendering`
3. 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.