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.

Objects in CrateDB are similar to JSON objects but are not exactly the same. However, you can insert objects as JSON strings.

---

Syntax

<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

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:

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

A STRICT object without any defined subcolumns will contain one unusable column that is always NULL.

---

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:

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:

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:

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

• Keys must be lowercase identifiers or quoted for mixed/camel case.

• Values can be literals, arrays, or nested objects.

Examples:

-- 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:

'{}'::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.