# Objects

An **object** is a collection of key-value pairs that can contain any data type, including nested objects (child objects). In CrateDB, an `OBJECT` column can be either **schemaless** or have a **defined (enforced) schema**.

{% hint style="info" %}
Objects in CrateDB are similar to JSON objects but are not exactly the same. However, you can insert objects as JSON strings.
{% endhint %}

***

### Syntax

```sql
<columnName> OBJECT
    [ ({DYNAMIC|STRICT|IGNORED}) ]
    [ AS ( <columnDefinition>* ) ]
```

* The only mandatory part is the keyword `OBJECT`.
* The **column policy** (`DYNAMIC`, `STRICT`, or `IGNORED`) is optional and defaults to `DYNAMIC`.
* If the list of subcolumns (`columnDefinition`) is omitted, the object will have **no schema**.
* For `DYNAMIC` objects, CrateDB will **create a schema upon first insert**.

***

### Example

```sql
CREATE TABLE my_table (
    title TEXT,
    quotation OBJECT,
    protagonist OBJECT(STRICT) AS (
        age INTEGER,
        first_name TEXT,
        details OBJECT AS (
            birthday TIMESTAMP WITH TIME ZONE
        )
    )
);

INSERT INTO my_table (
    title,
    quotation,
    protagonist
) VALUES (
    'Alice in Wonderland',
    {
        "words" = 'Curiouser and curiouser!',
        "length" = 3
    },
    {
        "age" = '10',
        "first_name" = 'Alice',
        "details" = {
            "birthday" = '1852-05-04T00:00Z'::TIMESTAMPTZ
        }
    }
);

SELECT
    protagonist['first_name'] AS name,
    date_format('%D %b %Y', 'GMT', protagonist['details']['birthday']) AS born,
    protagonist['age'] AS age
FROM my_table;
```

***

### Object Column Policies

#### STRICT

* Only allows subcolumns defined upfront in the schema.
* Inserts with undefined subcolumns will be rejected.

**Example:**

```sql
CREATE TABLE my_table (
    title TEXT,
    protagonist OBJECT(STRICT) AS (
        name TEXT
    )
);

INSERT INTO my_table (
    title,
    protagonist
) VALUES (
    'Alice in Wonderland',
    {
        "age" = '10'
    }
);
-- Fails: age is not defined in the schema
```

{% hint style="info" %}
A `STRICT` object without any defined subcolumns will contain one unusable column that is always `NULL`.
{% endhint %}

***

#### DYNAMIC (Default)

* Allows new subcolumns to be **added dynamically** on insert.
* New columns become part of the schema and are indexed.
* The schema update is permanent; the type of the new column is fixed.

**Example:**

```sql
CREATE TABLE my_table (
    title TEXT,
    quotation OBJECT(DYNAMIC) AS (
        words TEXT,
        length SMALLINT
    )
);

INSERT INTO my_table (
    title,
    quotation
) VALUES (
    'Alice in Wonderland',
    {
        "words" = 'DRINK ME',
        "length" = 2,
        "chapter" = 1  -- New column added dynamically
    }
);

SELECT quotation['chapter'], quotation['words'] FROM my_table ORDER BY chapter ASC;
```

> Dynamically added columns:
>
> * Are indexed but **not tokenized** for TEXT types.
> * Appear in `information_schema.columns` with fixed types.
> * Inserting incompatible types later will cause errors.

***

#### IGNORED

* Allows dynamic addition of subcolumns **without schema updates**.
* Dynamically added subcolumns are **not indexed**.
* Supports mixed types within the same subcolumn.
* Queries on these subcolumns do **not use indexes** and can be slower.

**Example:**

```sql
CREATE TABLE my_table (
    title TEXT,
    protagonist OBJECT(IGNORED) AS (
        name TEXT,
        chapter SMALLINT
    )
);

INSERT INTO my_table (
    title,
    protagonist
) VALUES (
    'Alice in Wonderland',
    {
        "name" = 'Alice',
        "chapter" = 1,
        "size" = { "value" = 10, "units" = 'inches' }
    }
);

INSERT INTO my_table (
    title,
    protagonist
) VALUES (
    'Alice in Wonderland',
    {
        "name" = 'Alice',
        "chapter" = 2,
        "size" = 'As big as a room'  -- Different type than previous insert
    }
);

SELECT protagonist['name'], protagonist['chapter'], protagonist['size'] FROM my_table ORDER BY protagonist['chapter'];
```

> **Important Notes:**
>
> * Filter and ordering operations on IGNORED subcolumns may be slow.
> * Aggregations can fail if subcolumn types differ between rows.
> * Explicit casts may be needed when querying these subcolumns.

***

### Object Literals

Objects can be inserted using **object literals**, which use curly braces `{}` with key-value pairs connected by `=`:

#### Syntax:

```sql
{ [ key = value [, ...] ] }
```

* **Keys** must be lowercase identifiers or quoted for mixed/camel case.
* **Values** can be literals, arrays, or nested objects.

#### Examples:

```sql
-- Empty object
{}

-- Boolean
{ my_bool_column = true }

-- Text
{ my_str_col = 'this is a text value' }

-- Numbers
{ my_int_col = 1234, my_float_col = 5.6 }

-- Array
{ my_array_column = ['v', 'a', 'l', 'u', 'e'] }

-- Camel case key (quoted)
{ "CamelCaseColumn" = 'this is a text value' }

-- Nested object
{ nested_obj_colmn = { int_col = 1234, str_col = 'text value' } }

-- Using placeholder parameter
{ my_other_column = ? }

-- Combined example
{ id = 1, name = 'foo', tags = ['apple'], size = 3.1415, valid = ? }
```

> **Note:** Object literals are similar but **not JSON**. For JSON usage, see below.

***

### Inserting Objects as JSON Strings

You can insert JSON strings by casting them explicitly or implicitly to `OBJECT` type.

#### Examples:

```sql
'{}'::object
'{ "my_bool_column": true }'::object
'{ "my_str_col": "this is a text value" }'::object
'{ "my_int_col": 1234, "my_float_col": 5.6 }'::object
'{ "my_array_column": ["v", "a", "l", "u", "e"] }'::object
'{ "CamelCaseColumn": "this is a text value" }'::object
'{ "nested_obj_col": { "int_col": 1234, "str_col": "foo" } }'::object
```

> **Tip:** Explicit casts improve query readability.

> **Note:** Placeholder parameters cannot be used inside JSON strings.
