JSON2 Type

JSON2 is a JSON type in GreptimeDB designed for logs and semi-structured data. It stores fields inside JSON in a structured, columnar form so that frequently used fields can be read, filtered, and aggregated efficiently like regular columns, while still preserving the flexibility of JSON for dynamic schemas.

note

JSON2 is currently in Beta, and some capabilities are still being improved.

Quick Start

The following example creates an API access log table, inserts a few request logs, and queries fields from JSON2. Fixed fields are stored in regular columns, while fields in attrs use JSON2 because their structure may vary but they are still queried frequently.

Create a table

When creating a table, you can declare a JSON2 column with the JSON2 type. Currently, JSON2 can only be used in append-only tables, so you must set 'append_mode' = 'true' when creating the table.

CREATE TABLE application_logs (
    ts TIMESTAMP TIME INDEX,
    app_name STRING,
    log_level STRING,
    `message` STRING,
    attrs JSON2,
) WITH (
    'append_mode' = 'true'
);

Insert JSON data

When writing to a JSON2 column, you can insert a JSON object, including an empty object ({}). SQL NULL and an omitted JSON2 column are also accepted and are distinct from an empty object. Root arrays, scalar JSON values, and the JSON literal null are not supported. The following data includes one successful request, one slow request, and one failed request:

INSERT INTO application_logs
VALUES
    (
        1,
        'checkout',
        'INFO',
        'request completed',
        '{"trace_id":"8f3a1c","user":{"id":1001,"name":"Alice"},"http":{"method":"POST","path":"/v1/orders","status":200},"items":[{"name":"book"}],"latency_ms":42.8}'
    ),
    (
        2,
        'checkout',
        'WARN',
        'slow request',
        '{"trace_id":"8f3a1d","user":{"id":1002,"name":"Bob"},"http":{"method":"POST","path":"/v1/orders","status":200},"items":[{"name":"pen"}],"latency_ms":386.4}'
    ),
    (
        3,
        'checkout',
        'ERROR',
        'request failed',
        '{"trace_id":"8f3a1e","user":{"id":1003},"http":{"method":"POST","path":"/v1/orders","status":500},"items":[{"name":"notebook"}],"latency_ms":71.2,"error":true}'
    );

Custom pipelines can also write JSON2 columns by using type: json2 in the transform configuration. See the pipeline configuration reference for the supported type-hint options.

Query JSON fields

You can read fields from JSON2 directly with dot paths:

SELECT
    ts,
    app_name,
    attrs.trace_id AS trace_id,
    attrs.user.name AS user_name,
    attrs.http.status AS status,
    attrs.latency_ms AS latency_ms,
    attrs.error AS error
FROM application_logs
ORDER BY ts;

The query result is:

ts	app_name	trace_id	user_name	status	latency_ms	error
1970-01-01 00:00:00.001	checkout	8f3a1c	Alice	200	42.8	NULL
1970-01-01 00:00:00.002	checkout	8f3a1d	Bob	200	386.4	NULL
1970-01-01 00:00:00.003	checkout	8f3a1e	NULL	500	71.2	true

You can also select the complete JSON2 value:

SELECT ts, attrs
FROM application_logs
ORDER BY ts;

You can also use JSON functions and cast the return type explicitly:

SELECT
    json_get(attrs, 'http.path')::STRING AS path,
    json_get(attrs, 'http.status')::BIGINT AS status,
    json_get(attrs, 'latency_ms')::DOUBLE AS latency_ms,
    json_get(attrs, 'error')::BOOLEAN AS error
FROM application_logs
WHERE json_get(attrs, 'http.status')::BIGINT >= 500
    OR json_get(attrs, 'latency_ms')::DOUBLE > 300
ORDER BY ts;

The query result is:

path	status	latency_ms	error
/v1/orders	200	386.4	NULL
/v1/orders	500	71.2	true

You can also aggregate fields, for example to count requests, errors, and average latency for each API path:

SELECT
    json_get(attrs, 'http.path')::STRING AS path,
    COUNT(*) AS requests,
    SUM(CASE WHEN json_get(attrs, 'error')::BOOLEAN THEN 1 ELSE 0 END) AS errors,
    ROUND(AVG(json_get(attrs, 'latency_ms')::DOUBLE), 1) AS avg_latency_ms
FROM application_logs
GROUP BY json_get(attrs, 'http.path')::STRING;

The query result is:

path	requests	errors	avg_latency_ms
/v1/orders	3	1	166.8

JSON2 Column Settings

JSON Field Type hints

JSON2 supports type hints for declaring concrete data types for selected subpaths. Type hints are recommended for frequently queried subpaths with known and stable types. These subpaths are stored using the specified types, providing query performance close to regular columns. JSON2 also validates their values during writes. Type hints are optional. For subpaths without type hints, JSON2 infers their types from the values written to the column.

The syntax for declaring type hints is:

json_column JSON2 (
    path.to.field DATA_TYPE [INVERTED INDEX]
)

Type hint paths use dot notation. For example, user.id refers to the following JSON path: {"user":{"id":...}}.

If a JSON key itself contains a dot, wrap that path segment in double quotes. For example, "service.name" means a key named service.name in the root object, not a nested path service.name.

Type hints currently support the following data types:

• STRING
• BIGINT / INT64
• BIGINT UNSIGNED / UINT64
• DOUBLE / FLOAT64
• BOOLEAN

Type hint paths are always nullable: a path can be missing or null in the written JSON. Missing fields are normalized to JSON null. Explicit NULL, NOT NULL, and DEFAULT options are not supported.

You can declare type hints directly in the CREATE TABLE statement. The following is an alternative way to create application_logs, with type hints for commonly queried subpaths in attrs:

CREATE TABLE application_logs (
    ts TIMESTAMP TIME INDEX,
    app_name STRING,
    log_level STRING,
    `message` STRING,
    attrs JSON2 (
        trace_id STRING,
        user.id BIGINT,
        user.name STRING,
        http.method STRING,
        http.path STRING,
        http.status BIGINT,
        latency_ms DOUBLE,
        error BOOLEAN
    )
) WITH (
    'append_mode' = 'true'
);

Inverted indexes on subpaths

You can create inverted indexes on JSON subpaths with type hints. For subpaths frequently used in filters, add INVERTED INDEX after the type to enable indexing. Indexes must be declared explicitly for each path; declaring a type hint or automatically expanding a path does not create an index.

The following example creates a table with indexed JSON subpaths:

CREATE TABLE indexed_logs (
    ts TIMESTAMP TIME INDEX,
    attrs JSON2 (
        service.name STRING INVERTED INDEX,
        http.status BIGINT INVERTED INDEX
    )
) WITH ('append_mode' = 'true');

This statement creates inverted indexes on the service.name and http.status subpaths in attrs.

Queries using dot syntax or json_get in WHERE clauses can use these indexes to accelerate filtering. You do not need to specify the indexes explicitly. For example:

SELECT * FROM indexed_logs
WHERE attrs.service.name = 'api' AND attrs.http.status >= 500;

SELECT * FROM indexed_logs
WHERE json_get(attrs, 'http.status') IN (400, 404, 500);

Inverted indexes support filters such as equality, range comparisons, BETWEEN, IN, and string regular expression matching with ~.

Control automatic path expansion

JSON2 automatically stores up to 100 unhinted leaf paths as structured columns. Use max_auto_expanded_paths to change this limit. The following is a separate table creation example:

CREATE TABLE application_logs (
    ts TIMESTAMP TIME INDEX,
    attrs JSON2 (
        max_auto_expanded_paths = 20,
        trace_id STRING
    )
) WITH (
    'append_mode' = 'true'
);

Set the option to 0 to disable automatic expansion. Type-hinted paths do not count against this limit. Fields beyond the limit remain in a special remainder field and can still be queried, so the option controls storage layout and performance, not the logical JSON schema.

Modify settings

The settings of an existing JSON2 column can be changed with ALTER TABLE ... MODIFY COLUMN. See Modify JSON2 settings.

Query JSON2

JSON2 supports accessing nested fields through the json_get function or dot syntax.

json_get function

json_get reads a nested field from JSON2 by path.

The syntax is:

json_get(json_column, 'path.to.field')

GreptimeDB infers the return type. See Type inference below for details.

json_get can be used in SELECT, WHERE, GROUP BY, and other SQL clauses that accept expressions. For example:

SELECT
    json_get(attrs, 'trace_id')::STRING AS trace_id,
    json_get(attrs, 'http.status')::BIGINT AS status,
    json_get(attrs, 'latency_ms')::DOUBLE AS latency_ms
FROM application_logs
WHERE json_get(attrs, 'http.status')::BIGINT >= 500;

The typed extraction functions json_get_string, json_get_int, json_get_float, and json_get_bool also accept JSON2 values. See JSON functions for details.

Dot syntax

You can read JSON2 subpaths directly with dot syntax and use zero-based subscripts to access array elements:

json_column.path.to.field
json_column.path[0].field

Dot syntax can be used in SELECT, WHERE, GROUP BY, and other SQL clauses that accept expressions. For example:

SELECT
    attrs.trace_id,
    attrs.http.status,
    attrs.latency_ms,
    attrs.items[0].name
FROM application_logs
WHERE attrs.http.status >= 500;

A missing path or an out-of-range array subscript returns NULL.

Type inference

When using json_get or dot syntax, GreptimeDB determines the return type according to the following rules.

If the accessed path has a type hint, the result uses the type specified by that hint. For example, if http.status has a BIGINT type hint, both json_get(attrs, 'http.status') and attrs.http.status return BIGINT.

For paths without type hints, GreptimeDB infers the return type from the query context, including explicit casts (::TYPE or CAST(... AS TYPE)), function arguments, and comparisons. For example:

-- Infer the type from the explicit cast target and read the field as DOUBLE
SELECT
    json_get(attrs, 'latency_ms')::DOUBLE AS latency_by_function,
    attrs.latency_ms::DOUBLE AS latency_by_dot
FROM application_logs;

-- Infer the type from function argument requirements
SELECT ABS(attrs.latency_ms) AS latency_ms
FROM application_logs;

-- Infer the type from comparison requirements
SELECT ts
FROM application_logs
WHERE attrs.http.status >= 500;

If a path has a type hint, the field is read using the declared type first; an outer explicit cast applies to the result.

If a path has no type hint and the query context cannot determine the return type, the result defaults to STRING. For example, without a type hint, both expressions below return STRING, even if http.status stores a number in the JSON:

SELECT
    json_get(attrs, 'http.status') AS status_by_function,
    attrs.http.status AS status_by_dot
FROM application_logs;

Current limitations

JSON2 is currently in Beta and has the following limitations:

• JSON2 columns can only be used in append-only tables.
• The JSON root must be an object. Root arrays, strings, numbers, booleans, and the JSON literal null are not supported.
• Type hints support only the types listed above and cannot traverse arrays.
• Structured expansion and type hint paths support at most 50 nested path segments. Deeper unhinted values remain queryable in the special remainder field.