JSON data

CrateDB combines the flexibility of NoSQL document stores with the power of SQL. It enables you to store, query, and index semi-structured JSON data using standard SQL, making it an excellent choice for applications that handle diverse or evolving schemas.

CrateDB’s support for dynamic objects, nested structures, and bracket notation querying brings the best of both relational and document-based data modelling — without leaving the SQL world.

A Simple Table with JSON

CrateDB allows you to define object columns that can store JSON-style data structures.

CREATE TABLE events (
  id TEXT PRIMARY KEY,
  timestamp TIMESTAMP,
  payload OBJECT
);

This allows inserting flexible, nested JSON data into payload:

{
  "user": {
    "id": 42,
    "name": "Alice"
  },
  "action": "login",
  "device": {
    "type": "mobile",
    "os": "iOS"
  }
}

Column Policy — Strict vs Dynamic

You can control how CrateDB handles unexpected fields in an object column:

Column Policy

Behavior

DYNAMIC

(Default) New fields are automatically added to the schema at runtime

STRICT

Only explicitly defined fields are allowed

IGNORED

Extra fields are stored but not indexed or queryable

Let’s evolve our table to restrict the structure of payload:

CREATE TABLE events2 (
  id TEXT PRIMARY KEY,
  timestamp TIMESTAMP,
  payload OBJECT(STRICT) AS (
    temperature DOUBLE,
    humidity DOUBLE
  )
);

You can no longer use fields other than temperature and humidity in the payload object.

Querying JSON Fields

Use bracket notation to access nested fields:

SELECT payload['temperature'], payload['humidity']
FROM events2
WHERE payload['temperature'] >= 20.0;

CrateDB also supports filtering, sorting, and aggregations on nested values:

-- count events with high humidity
SELECT COUNT(*) AS high_humidity_events
FROM events2
WHERE payload['humidity'] > 70

Note

Bracket notation works for both explicitly and dynamically added fields.

Querying DYNAMIC OBJECTs Safely

When working with dynamic objects, some keys may not exist. CrateDB provides the error_on_unknown_object_key session setting to control behavior in such cases.

By default, CrateDB will raise an error if any of the queried object keys are unknown. When adjusting this setting to false, it will return NULL as the value of the corresponding key.

cr> CREATE TABLE events (payload OBJECT(DYNAMIC));
CREATE OK, 1 row affected  (0.563 sec)

cr> SELECT payload['unknown'] FROM events;
ColumnUnknownException[Column payload['unknown'] unknown]

cr> SET error_on_unknown_object_key = false;
SET OK, 0 rows affected  (0.001 sec)

cr> SELECT payload['unknown'] FROM events;
+-------------------+
| payload['unknown']|
+-------------------+
SELECT 0 rows in set (0.051 sec)

Aggregating JSON Fields

CrateDB allows full SQL-style aggregations on nested fields:

SELECT AVG(payload['temperature']) AS avg_temp
FROM events
WHERE payload['humidity'] > 20.0;

Combining Structured & Semi-Structured Data

As you can see in the events table, CrateDB supports hybrid schemas, mixing standard columns with JSON fields.

This allows you to:

  • Query by fixed attributes (temperature)

  • Flexibly store structured or unstructured metadata in payload

  • Add new fields on the fly without altering a table, skipping migrations

Indexing Behavior

CrateDB automatically indexes object fields if:

  • Column policy is DYNAMIC

  • Field type can be inferred at insert time

You can also explicitly define and index object fields. Let’s extend the payload with a message field with full-text index, and also disable index for humidity:

CREATE TABLE events3 (
  id TEXT PRIMARY KEY,
  timestamp TIMESTAMP,
  tags ARRAY(TEXT),
  payload OBJECT(DYNAMIC) AS (
    temperature DOUBLE,
    humidity DOUBLE INDEX OFF,
    message TEXT INDEX USING FULLTEXT
  )
);

Note

When using dynamic objects too many columns could be created, the default per table is 1000, more could impact performance. Use STRICT or IGNOREDif needed.

Object fields are treated as any other column, therefore GROUP BY, HAVING, and window functions are supported.

See also