chdb fails to resolve JSON field access in materialized views

[m-dressler]

chdb throws a NOT_FOUND_COLUMN_IN_BLOCK error when a materialized view attempts to access JSON fields using dot notation (e.g., meta.total) during INSERT operations, even though the exact same field access works correctly in regular SELECT queries. This issue does not occur in standard ClickHouse deployments.

Example

import { Session } from 'chdb';
const session = new Session();

// Setup
session.query('SET allow_experimental_json_type = 1');
session.query('CREATE DATABASE IF NOT EXISTS test');
session.query('USE test');

// Create a simple table with JSON column
console.log('1. Creating table with JSON column...');
session.query(`
        CREATE TABLE IF NOT EXISTS events (
            id UInt32,
            type String,
            meta JSON
        ) ENGINE = Memory
    `);

// Test 1: Verify JSON field access works in SELECT
console.log('2. Testing JSON field access in SELECT query...');
session.query(
    `INSERT INTO events VALUES (1, 'purchase', '{"total": 500, "currency": "USD"}')`
);

const selectResult = session.query(`
        SELECT 
            type,
            meta.total as total_value,
            meta.currency as currency
        FROM events
    `);
console.log('   ✓ SELECT with JSON field access works:', selectResult);

// Create aggregate table
session.query(`
        CREATE TABLE IF NOT EXISTS events_summary (
            type String,
            total_sum Int64
        ) ENGINE = Memory
    `);

// Test 2: Create MV with JSON field access and try INSERT
console.log('3. Creating materialized view with JSON field access...');
session.query(`
        CREATE MATERIALIZED VIEW IF NOT EXISTS mv_summary
        TO events_summary
        AS SELECT
            type,
            sum(meta.total::Int64) as total_sum
        FROM events
        GROUP BY type
    `);

console.log('4. Testing INSERT with materialized view active...');
try {
    session.query(`
            INSERT INTO events VALUES 
            (2, 'purchase', '{"total": 1000, "currency": "EUR"}')
        `);
    console.log('   ✓ INSERT successful (unexpected - bug may be fixed!)');
} catch (error) {
    console.log('   ✗ INSERT FAILED');
    console.log('   Error:', error.message);
    console.log(
        '   This is the bug: MV cannot access meta.total during INSERT, even though the same access works in SELECT queries.'
    );
}
session.cleanup();

Environment

• chdb version: 1.3.0
• Node.js version: v20.x
• Operating System: macOS / Linux
• ClickHouse compatibility: The same schema and queries work correctly in ClickHouse v23+

[m-dressler]

Current workaround: Use JSONExtractRaw(toString(<COLUMN>), <PATH>) in materialized view JSON access

[auxten]

@wudidapaopao Pls have a look when you have time

[wudidapaopao]

Hi @m-dressler, I tested ClickHouse version 24.8.1 and found it also throws errors, whereas versions 25.x and above do not. Therefore, we maybe plan to upgrade the chdb version, as the new chdb version uses the ClickHouse kernel version 25.x.
see https://fiddle.clickhouse.com/de0e8973-25b5-43f8-b207-49877fe7f7b2

[m-dressler]

@wudidapaopao yes, seems to be the CH version. I think migrating to latest would probably make sense for a couple of reasons, but might also be worth considering BYO CH version if feasible so chdb need not be republished for every CH version.

[wudidapaopao]

Yes, we could consider allowing users to install different versions of libchdb.so themselves, which would offer greater flexibility. The only potential downside is that users might end up installing an incompatible version of libchdb.so.