Migrate from Object(JSON) to the new JSON type

Step 1: Identify columns to migrate

If you manage multiple instances, you can first run an aggregate query to identify instances and tables that still use the legacy type:

SELECT
    uniqExact(spoken_name)                         AS instances,
    groupUniqArray(spoken_name),
    uniqExact(database, name)                      AS tables,
    any(create_table_query)
FROM merge('tables.*')
WHERE create_table_query LIKE '%Object(\'json\')%'
  AND spoken_name NOT LIKE '%stress%'
  AND scrape_time_microseconds > now() - INTERVAL 40 DAY
  AND database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA')

On a single instance, run the following SQL query to list all columns that use the legacy Object(JSON) type:

SELECT database, table, name AS column, type
FROM system.columns
WHERE type LIKE '%Object(%json%)%'
  AND database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA')
ORDER BY database, table, name;

The query returns the database name, table name, column name, and current type for all columns that require migration.

Step 2: Run the migration

You can choose one of the following methods based on your business requirements.

ALTER TABLE <db>.<table>
    MODIFY COLUMN <column> JSON
    SETTINGS mutations_sync = 1;
-- On versions earlier than 25.3, enable enable_json_type = 1 in the session

CREATE TABLE new_table AS old_table;

ALTER TABLE new_table MODIFY COLUMN `your_json_column` JSON;

INSERT INTO new_table SELECT * FROM old_table;

Step 3: Adapt queries after migration

After the migration, write operations (INSERT) typically do not require changes, but read operations (SELECT) may need adaptation. The key difference is that path access on the legacy Object type returns inferred concrete types, while path access on the new JSON type returns the Dynamic type.

Write operations (no changes required)

Both types accept JSON documents as input. Insert code typically does not require changes. The only exception is when your code hardcodes the type name as a string, such as in DDL statements or CAST target types that use Object('json'). In these cases, change the type name to JSON. Pure data write paths are not affected.

Read operations (review required)

Review and adapt your queries based on the following scenarios:

Examples of the new syntax:

-- Scalar type annotation
SELECT json.a.g.:Float64, json.d.:Date FROM t;

-- GROUP BY / ORDER BY requires explicit settings
SELECT json.repo.name, count() AS c
FROM t
GROUP BY json.repo.name
ORDER BY c DESC
SETTINGS allow_suspicious_types_in_group_by = 1, allow_suspicious_types_in_order_by = 1;

-- Object arrays: [] denotes the array level, compatible with ARRAY JOIN
SELECT json.payload.commits[].author.name FROM t;

-- Read entire sub-object
SELECT json.^metadata FROM t;

Step 4: Verify the migration

Confirm that no Object(JSON) columns remain in the cluster:

SELECT count() FROM system.columns
WHERE type LIKE '%Object(%json%)%'
  AND database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA');

Confirm that all JSON-related mutations are complete:

SELECT count() FROM system.mutations
WHERE command ILIKE '%JSON%' AND is_done = 0;

If both queries return 0, the migration is complete for the instance.

Memory limit exceeded on large tables

Large tables may trigger a memory limit error when the entire column is cast to JSON. A typical error message is as follows:

latest_fail_reason: (total) memory limit exceeded: would use 7.20 GiB
    ... while executing 'FUNCTION _CAST(<column> :: 0, 'JSON' :: 1) -> _CAST(<column>, 'JSON') JSON'
    (while reading from part .../all_1_1_0 located on disk s3WithKeeperDisk of type s3)
    While executing MergeTreeSequentialSource
latest_fail_error_code_name: MEMORY_LIMIT_EXCEEDED

You can use the following methods to mitigate this issue:

• Scale up or temporarily increase the instance memory limit.

• Upgrade to a later version (we recommend upgrading to 25.10) and retry.

• Migrate large tables partition by partition to reduce peak memory usage per mutation.

Monitor migration progress

Run the following query to check the progress of in-progress JSON-related mutations:

SELECT *
FROM system.mutations
WHERE command ILIKE '%JSON%'
  AND is_done = 0;

Pay attention to the parts_to_do (remaining parts), is_done, and latest_fail_reason fields.