Data Types

Answer checklist

When answering about storing and reading JSON objects:

• Use the new JSON column type, introduced in ClickHouse >= 24.8.

• Say JSON is no longer experimental since ClickHouse 25.3; on older supported versions, enable allow_experimental_json_type.

• State the version policy in your explanation AND inline in the code. When you set allow_experimental_json_type (or any allow_experimental_*_type) in code, you must do BOTH of the following:

  1. Put an inline comment directly above the setting that names the version where the type was introduced and the version where it became non-experimental. The comment is the durable version provenance — it lives in the user’s source file long after the chat reply is gone.
  2. Repeat the version policy in your prose reply.

  For the JSON column type the inline comment must look like:

  tsCopy

  clickhouse_settings: {
    // JSON type introduced in ClickHouse 24.8, non-experimental since 25.3.
    // This setting is required only on 24.8–25.2; harmless on >= 25.3.
    allow_experimental_json_type: 1,
  }

  Without the inline comment, a reader on a newer server has no idea the setting is a no-op and a reader on an older server has no idea why it’s required.

• Insert real JS objects with format: 'JSONEachRow'; do not JSON.stringify() the column value.

• Read with a JSON output format such as JSONEachRow and resultSet.json(); JSON column values come back as parsed JS objects.

Dynamic, Variant(...), JSON

Outputs:

Notes

• The JSON column type accepts a real JS object on insert and returns one on select — no need for JSON.stringify / JSON.parse in your app code.
• A JS number written into a Dynamic or Variant column defaults to Int64 on the server. In JSON formats, output_format_json_quote_64bit_integers controls how 64-bit integers are returned: 1 returns them as JSON strings, while 0 returns them as JSON numbers (and 0 is the default since CH 25.8). In JS, large 64-bit integers returned as numbers can lose precision, so use quoted output if you need exact integer values in application code.
• Use variantType(...), dynamicType(...) to introspect what the server ended up storing.

Time and Time64(p)

Time is signed seconds (-999:59:59 … 999:59:59). Time64(p) adds sub-second precision (p digits, up to 9 for nanoseconds). Both require enable_time_time64_type: 1 on >= 25.6.

Notes

• Pass values as strings in the HH:MM:SS[.fraction] format. Negatives are supported; the magnitude can exceed 24 hours.
• For Time64(p) with p > 3, do not use JS Date — it tops out at millisecond precision and will silently truncate. Store nanosecond values separately and provide on stringify as needed.

QBit (vector search)

QBit(element_type, dimension) stores float vectors in bit-sliced (“transposed”) form so approximate vector search can read only the most significant bit planes at query time, trading precision for I/O and CPU.

• element_type: BFloat16 | Float32 | Float64.
• dimension: number of elements in each vector.

Introduced in ClickHouse 25.10 as an experimental type (gated by allow_experimental_qbit_type) and GA on 26.x; the setting is a no-op on newer servers but required on 25.10.

Internally a QBit column is a Tuple(FixedString(N), ...) of bit planes, so the raw bytes are not valid UTF-8. JSON* formats handle this transparently: the server serializes the column as the original numeric array on SELECT and accepts the same array shape on INSERT. Query the column as a vector and let ClickHouse handle the bit-plane layout — don’t feed raw FixedString bytes through JSON yourself.

Notes

• Insert and read QBit columns as plain numeric arrays with JSONEachRow; the server transposes them into bit planes for you.
• Use L2DistanceTransposed(vec, ref, bits) for approximate nearest-neighbour search. Bind ref and bits via query_params ({ref:Array(Float32)}, {bits:UInt8}) — never interpolate them into the SQL string.
• The bits argument is the precision in bits: lower values read fewer bit planes (less I/O, faster, less accurate); higher values are more precise.
• The bit-plane subcolumns (vec.N) are FixedString and not valid UTF-8. Selecting them directly with a JSON* format forces the server to escape every byte as \uXXXX. If you need the raw planes, use a binary format such as RowBinary, or read them as hex/base64 (e.g. hex(vec.1)) to keep the JSON output UTF-8 safe.

Common pitfalls

• Targeting old ClickHouse servers without the allow_experimental_* setting. On < 25.3, CREATE TABLE will fail without them.
• Expecting JSON-column reads to be raw strings. They come back as parsed objects in JSON formats.
• Inserting Time64(9) from JS Date and losing precision. Use a string instead.
• Reading a Variant/Dynamic value of type Int64 and being surprised it’s a string. That’s the standard 64-bit-integers-in-JSON behavior; see the troubleshooting skill if you need to change it.
• Avoid parsing Variant/Dynamic/JSON columns that mix strings and 64-bit without checking their returned types first. Otherwise a number stored in a string will come back as a number or vice versa.
• Selecting QBit bit-plane subcolumns (vec.N) directly in JSON formats. They are FixedString and not UTF-8; read them as hex/base64 or use a binary format. Read the whole vector (vec) as a numeric array instead.