Clickhouse JSON Ephemeral Column

Question

Can't get the example from the clickhouse docs to work:

DROP TABLE IF EXISTS github_json;

SET allow_experimental_object_type = 1;
CREATE table github_json
(
   event_type LowCardinality(String) DEFAULT JSONExtractString(message_raw, 'type'),
   repo_name LowCardinality(String) DEFAULT JSONExtractString(message_raw, 'repo.name'),
   message JSON DEFAULT message_raw,
   message_raw String EPHEMERAL
) ENGINE = MergeTree ORDER BY (event_type, repo_name);

Insertion thus requires a modified structure - note how the JSON is parsed as a string inside message_raw.

INSERT INTO github_json (message_raw) FORMAT JSONEachRow {"message_raw": "{\"type\":\"PushEvent\",
\"created_at\": \"2022-01-04 07:00:00\", \"actor\":{\"avatar_url\":\"https://avatars.githubusercontent.com/u/41898282?\",
\"display_login\":\"github-actions\",\"gravatar_id\":\"\",\"id\":41898282,\"login\":\"github-actions[bot]\",
\"url\":\"https://api.github.com/users/github-actions[bot]\"},\"repo\":{\"id\":410071248,\"name\":\"pioug/yield-data\",
\"url\":\"https://api.github.com/repos/pioug/yield-data\"}}"}

Keep getting the error:

Received exception from server (version 23.11.2):
Code: 117. DB::Exception: Received from localhost:9000. DB::Exception: Cannot parse object: while executing 'FUNCTION _CAST(message_raw :: 0, 'Object(\'json\')' :: 4) -> _CAST(message_raw, 'Object(\'json\')') Object('json') : 3'. (INCORRECT_DATA)

Answer 1

Try this:

CREATE table github_json
(
   event_type LowCardinality (String) DEFAULT JSONExtractString (message_raw, 'type'),
   repo_name LowCardinality (String) DEFAULT JSONExtractString (JSONExtractRaw (message_raw, 'repo'), 'name'),
   message JSON DEFAULT message_raw,
   message_raw String EPHEMERAL
) ENGINE = MergeTree ORDER BY (event_type, repo_name)

and

INSERT INTO github_json (message_raw) 
VALUES ('{"type":"PushEvent", "repo":{"name":"pioug/yield-data"}}')