JSON Columns

JSON columns store schema-flexible JSON objects in a single column. A JSON column accepts nested fields, mixed structures, and arrays without defining each field individually in the transform, and sub-fields are stored and queryable as they arrive. Use a JSON column when the shape of incoming data varies or isn't known in advance, such as a catch-all field for log and event attributes.

This feature was introduced in Hydrolix version 6.1.5

Performance notes

JSON columns include several performance improvements:

• Tables with multiple JSON columns benefit from improved compression at ingest. Schema-based sorting now operates across all JSON columns in a block, not only the first.
• Queries on deeply nested paths run faster and use less memory. Subgroup subdivision recurses through multiple nesting levels, reading only the relevant subgroup rather than materializing the full column.

Define a JSON column

Set "type": "json" in the datatype block of an output column:

JSON Column Definition

{
  "name": "my_transform",
  "type": "json",
  "table": "project.table",
  "settings": {
    "output_columns": [
      {
        "name": "timestamp",
        "datatype": {
          "type": "datetime",
          "format": "2006-01-02T15:04:05Z",
          "primary": true
        }
      },
      {
        "name": "event_data",
        "datatype": {
          "type": "json"
        }
      }
    ]
  }
}

With this transform, all sub-fields of event_data are stored and queryable as event_data.field_name. Arrays in the column are stored. No additional configuration is required.

Exclude sub-fields

Use json_skip and json_skip_regexp in the column's datatype block to prevent specific sub-fields from being ingested, stored, or indexed. Fields excluded this way aren't queryable.

json_skip accepts a list of exact field names. json_skip_regexp accepts a list of regular expression patterns matched against field names.

Exclude Sub-Fields

{
  "name": "json_col",
  "datatype": {
    "type": "json",
    "json_skip": ["int32_col"],
    "json_skip_regexp": ["^uint3.*"]
  }
}

Given input:

{"json_col": {"int32_col": 20, "string_col": "hello", "uint32_col": 300}}

Querying json_col returns only the fields that weren't excluded:

{"json_col": {"string_col": "hello"}}

json_skip and json_skip_regexp can also be set at the transform settings level, which applies the exclusion to all JSON columns in the transform. See Exclude fields from JSON columns.

Ignore a JSON column

Set ignore: true on a JSON column to parse the field from the input but exclude it from storage and the table schema. The column isn't queryable and doesn't appear in DESCRIBE output.

Ignored JSON Column

{
  "name": "ignored_json",
  "datatype": {
    "type": "json",
    "index": true,
    "ignore": true
  }
}

Given input rows that include ignored_json, Hydrolix parses but discards the field at ingest:

{"input_payload": "row_a", "timestamp": "2026-05-15 20:18:57"}
{"input_payload": "row_b", "timestamp": "2026-05-15 20:18:57"}

Querying the column directly returns an error:

SELECT ignored_json FROM test_project.ignore_json LIMIT 1
-- Code: 47. DB::Exception: Missing columns: 'ignored_json' (UNKNOWN_IDENTIFIER)

Virtual JSON columns

Set virtual: true on a JSON column to create a column that isn't mapped to any field in the incoming data. A virtual JSON column derives its value from a JavaScript expression in the script field.

Note

JSON columns support virtual: true, but only the script pairing is confirmed to work. Using default with a virtual JSON column may throw an error.

Virtual JSON Column with Script

{
  "name": "v_json_script",
  "datatype": {
    "type": "json",
    "index": true,
    "virtual": true,
    "script": "({computed: true, n: 42})"
  }
}

The script returns a JavaScript object, which Hydrolix stores as the JSON value for every ingested row. Because v_json_script has no mapped input field, Hydrolix populates it from the script at ingest:

{"input_payload": "row_a", "v_json_script": {"computed": true, "n": 42}}
{"input_payload": "row_b", "v_json_script": {"computed": true, "n": 42}}

Arrays in JSON columns

JSON columns can contain arrays of scalar values or arrays of JSON objects. Hydrolix detects and stores arrays at ingest without schema changes.

Supported element types

Arrays in JSON columns support these element types:

• Int8, Int32, Int64
• UInt8, UInt32, UInt64
• Float64
• String
• Array(T) (nested arrays)
• JSON

Array elements that are JSON objects, including map-like objects with varying keys, are stored as JSON elements.

Access array fields at query time

Use the field[].subfield notation to access sub-fields nested in an array of JSON objects.

Array Field Access

-- Retrieve a field from each element in an array of JSON objects
SELECT event_data.records[].traceId FROM my_table

-- Filter rows where any element has a matching value
SELECT * FROM my_table WHERE has(event_data.records[].service, 'db')

Filters using [].subfield paths are index-accelerated: Hydrolix uses the same block-skipping as scalar columns, so queries scan only the relevant partitions.

For scalar arrays (Array(String), Array(Int64), and similar), element access uses 1-based indexing:

Scalar Array Element Access

-- Access the first element of a scalar array field (1-based)
SELECT event_data.tags[1] FROM my_table

-- Filter where the first tag matches
SELECT * FROM my_table WHERE event_data.tags::Array(String)[1] = 'production'

Filters on a positional element need the explicit array cast (::Array(String) in this example) to use the index. Without the cast, WHERE event_data.tags[1] = 'production' returns correct results but scans without index acceleration. Whole-array comparisons, such as WHERE event_data.tags = ['production', 'us-west'], use the index without a cast.

Indexes

Hydrolix creates indexes at ingest for fields in JSON columns, including array fields. Index-based filtering applies to scalar sub-fields and [].subfield paths. See JSON column indexing for configuration options and details on which fields are indexed.

JSON column indexing

Hydrolix creates indexes at ingest for all fields in JSON columns, except floating-point values. No configuration is required.

At query time, Hydrolix uses these indexes for two patterns: homogeneous fields and simple arrays. A homogeneous field is a JSON sub-field whose values all have a single consistent type across the records in a partition. For example, data.age is always an integer, or data.country is always a string. A simple array is an array of primitive types such as Array(Int64) or Array(String).

Fields with mixed types in a single partition aren't used for index lookups, but Hydrolix still writes their indexes.

Mixed types and key changes

JSON columns support flexible schemas. If a field's type changes between partitions, each partition with a consistent type uses its index independently. If a field's type changes in a single partition, index lookups are skipped for that partition.

Adding or removing keys between records doesn't affect indexing. Each sub-column is indexed independently. Queries against json_col.field return null for rows where field isn't present.

To verify that a query uses JSON indexes, set hdx_query_debug=true. The indexes_used field lists JSON sub-columns by their full path, such as humans.name.

Exclude fields from JSON columns

The json_skip and json_skip_regexp transform settings exclude matching fields from JSON columns entirely. Excluded fields aren't stored, indexed, or queryable.

Example: Exclude Fields by NameExample: Exclude Fields by Pattern

"settings": {
    "json_skip": ["trace_id", "request_body"],
    "output_columns": [...]
}

"settings": {
    "json_skip_regexp": ["^debug_.*", ".*_raw$"],
    "output_columns": [...]
}

Explicit index: true/false settings on individual output columns take precedence over automatic indexing.

Summary tables

Summary tables support JSON columns and their sub-fields in summary SQL transform, table storage format, and in queries. Address a sub-field by its full path, such as event_data.status, in the summary SQL and in queries against the summary table.

Limitations

• Map of JSON may cause column explosion if map keys are unique across rows. Column explosion is a large number of distinct column paths that degrades query performance.
• Hydrolix stores IP, UUID, and Datetime values inside JSON as strings. To use these values as their intended types in SQL queries, apply explicit type conversion functions.
• Hydrolix stores UInt64 values above 9,223,372,036,854,775,807 as Float64, which may cause precision loss. To preserve the exact value, ingest the field into a string column instead of a JSON column.
• Accessing JSON fields in subqueries requires the hdx_allow_experimental_analyzer=1 query setting.
• Sorting directly on a JSON sub-field, such as ORDER BY event_data.tags, fails with Code: 44 ... Variant/Dynamic types are not allowed in ORDER BY keys. Cast the field to a concrete type, for example ORDER BY event_data.tags::Array(String), or set allow_suspicious_types_in_order_by=1.