DBeaver Problems Querying JSON data types

[pkutaj]

Description

Users are unable to effectively view and query JSON data types from ClickHouse columns using popular SQL clients like DBeaver (even with the latest ClickHouse driver) and, allegedly, Metabase. I've reproduced with DBeaver

Steps to reproduce

1. Create Table:

CREATE TABLE `test_json_dbeaver`
(
`data` JSON
)
ENGINE = MergeTree()
ORDER BY tuple();

2. Populate:

INSERT INTO `test_json_dbeaver` VALUES
('{"user_id": 12345, "session_start": "2024-01-15T10:30:00Z", "device": "mobile", "metadata": {"browser": "Safari", "version": 17.2}}'),
('{"customer_ref": "CUST_789", "timestamp": 1705312200, "platform": "web", "details": {"ip": "192.168.1.1", "country": "US"}}'),
('{"order_number": 98765, "created_at": "2024-01-15", "channel": "api", "payload": {"items": [{"id": 1, "qty": 2}]}}'),
('{"event_id": "evt_001", "occurred": 1705312800, "source": 42, "context": {"user_agent": "Mozilla/5.0", "referrer": null}}'),
('{"transaction_id": "tx_12345", "date": "2024-01-15T11:00:00", "amount": 199.99, "extra": {"currency": "USD", "fees": 2.99}}'),
('{"record_id": 555, "processed_on": 1705313400, "status": true, "info": {"processor": "system_a", "retry_count": 0}}'),
('{"batch_num": "B001", "run_time": "2024-01-15T11:30:00Z", "success": false, "errors": ["timeout", "connection_lost"]}'),
('{"request_id": 777888, "logged_at": 1705314000, "endpoint": "/api/v1/data", "response": {"code": 200, "size": 1024}}'),
('{"session_token": "tok_abcdef", "valid_until": "2024-01-16T10:30:00", "role": "admin", "permissions": ["read", "write", "delete"]}'),
('{"notification_id": 99999, "sent": 1705314600, "recipient": "[email protected]", "content": {"subject": "Welcome", "body": "Hello!"}}'),
('{"analytics_id": "ana_123", "captured": "2024-01-15T12:00:00Z", "page": "/home", "visitor": {"id": 12345, "returning": true}}');

3. Connect with DBeaver: Configure DBeaver to connect to ClickHouse Cloud using the latest driver.
4. Execute Query (demonstrates issue with filtering/displaying diverse JSON):

SELECT
data.permissions,
data.errors,
data.grades,
data.subjects
FROM default.`00027869`
WHERE data.permissions IS NOT NULL
OR data.errors IS NOT NULL
OR data.grades IS NOT NULL
LIMIT 10;

Expected/ClickHouse Output:

┌─data.permissions──────────┬─data.errors───────##────────────┬─data.grades─┬─data.subjects─┐
1. │ ᴺᵁᴸᴸ                      │ ['timeout','connection_lost'] │ ᴺᵁᴸᴸ        │ ᴺᵁᴸᴸ          │
2. │ ['read','write','delete'] │ ᴺᵁᴸᴸ                          │ ᴺᵁᴸᴸ        │ ᴺᵁ# ᴸᴸ          │
└───────────────────────────┴───────Er────────────────────────┴─────────────┴───��ro�───────────┘

Observed DBeaver Output (partial, showing display issue):

| data.permissions | data.errors  | data.grades | data.subjects |
|------------------|--------------|-------------|---------------|
|                  | [ArrayValue] |             |               |
| [ArrayValue]     |              |             |               |

Environment

• [x] Cloud
• Client version: 25.4
• Language version: EN
• OS: Mac OS

ClickHouse Server

• ClickHouse Server version: 25.4
• ClickHouse Server non-default settings, if any:
• CREATE TABLE statements for tables involved:
• Sample data for all these tables, use clickhouse-obfuscator if necessary