JSON2 shipped in GreptimeDB v1.2.0. When should you use it over JSON? JSON vs JSON2: a visual guide below. JSON2 is Beta, append-only. Get started: https://docs.greptime.com/user-guide/logs/json2/…

JSON2 Type | GreptimeDB Documentation

Source: https://docs.greptime.com/user-guide/logs/json2/ 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 inattrsuse 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 theJSON2type. 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. The following data includes one successful request, one slow request, and one failed request:

INSERT INTO application_logsVALUES    (        1,        'checkout',        'INFO',        'request completed',        '{"trace_id":"8f3a1c","user":{"id":1001,"name":"Alice"},"http":{"method":"POST","path":"/v1/orders","status":200},"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},"latency_ms":386.4}'    ),    (        3,        'checkout',        'ERROR',        'request failed',        '{"trace_id":"8f3a1e","user":{"id":1003},"http":{"method":"POST","path":"/v1/orders","status":500},"latency_ms":71.2,"error":true}'    );

Custom pipelines can also write JSON2 columns by usingtype: json2in the transform configuration. See thepipeline configuration referencefor 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 errorFROM application_logsORDER BY ts;

The query result is:

tsapp_nametrace_iduser_namestatuslatency_mserror1970-01-01 00:00:00.001checkout8f3a1cAlice20042.8NULL1970-01-01 00:00:00.002checkout8f3a1dBob200386.4NULL1970-01-01 00:00:00.003checkout8f3a1eNULL50071.2trueYou can also select the complete JSON2 value:

SELECT ts, attrsFROM application_logsORDER 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')::INT8 AS status,    json_get(attrs, 'latency_ms')::DOUBLE AS latency_ms,    json_get(attrs, 'error')::BOOLEAN AS errorFROM application_logsWHERE json_get(attrs, 'http.status')::INT8 >= 500    OR json_get(attrs, 'latency_ms')::DOUBLE > 300ORDER BY ts;

The query result is:

pathstatuslatency_mserror/v1/orders200386.4NULL/v1/orders50071.2trueYou 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_msFROM application_logsGROUP BY json_get(attrs, 'http.path')::STRING;

The query result is:

pathrequestserrorsavg_latency_ms/v1/orders31166.8## Syntax​

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 [NULL | NOT NULL] [DEFAULT literal])

Type hint paths use dot notation. For example,user\.idrefers 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 namedservice\.namein the root object, not a nested pathservice\.name.

Type hints currently support the following data types:

• STRING
• BIGINT
• BIGINT UNSIGNED
• DOUBLE
• BOOLEAN

Type hints allowNULLby default. If you specifyNOT NULL, that path must exist in the written JSON.

You can declare type hints directly in theCREATE TABLEstatement. The following example defines type hints for commonly queried subpaths in theattrscolumn:

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 DEFAULT 'anonymous',        http.method STRING,        http.path STRING,        http.status BIGINT,        latency_ms DOUBLE,        error BOOLEAN DEFAULT false    )) WITH (    'append_mode' = 'true');

json\_getUDF​

json\_getreads a nested field from JSON2 by path. It returns a string by default. If you want to specify the return type directly, add a cast after the function.

The syntax ofjson\_getis:

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

json\_getcan be used inSELECT,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_msFROM application_logsWHERE json_get(attrs, 'http.status')::BIGINT >= 500;

The typed extraction functionsjson\_get\_string,json\_get\_int,json\_get\_float, andjson\_get\_boolalso accept JSON2 values. SeeJSON functionsfor details.

Dot syntax​

You can read JSON2 subpaths directly with dot syntax:

json_column.path.to.field

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

SELECT    attrs.trace_id,    attrs.http.status,    attrs.latency_msFROM application_logsWHERE attrs.http.status >= 500;

Control automatic path expansion​

By default, JSON2 expands compatible unhinted leaf paths into structured columns. Usemax\_auto\_expanded\_pathsto limit how many paths are expanded:

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 to0to disable automatic expansion. Type-hinted paths do not count against this limit. Fields beyond the limit remain in a specialremainderfield and can still be queried, so the option controls storage layout and performance, not the logical JSON schema.

Current limitations​

JSON2 is currently in Beta and has the following limitations:

• JSON2 columns can only be used in append-only tables.
• Each non-NULL JSON root must be a non-empty object. Root arrays, strings, numbers, booleans, the JSON literalnull, and empty objects are not supported.
• Array elements cannot be accessed with subscript syntax.
• 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 specialremainderfield.