Handling JSON-encoded Kafka topics in Materialize isn’t fun. We’ve taken the
first step to improve JSON support in the Kafka source by introducing a new
FORMAT JSON option
that decodes messages as jsonb, saving you some conversion typing in
comparison to FORMAT BYTES.
The old way
CREATE SOURCE json_source
FROM KAFKA CONNECTION kafka_connection (TOPIC 'ch_anges')
FORMAT BYTES
WITH (SIZE = '3xsmall');
CREATE VIEW extract_json_source AS
SELECT
(data->>'field1')::boolean AS field_1,
(data->>'field2')::int AS field_2,
(data->>'field3')::float AS field_3
-- This is not fun
FROM (SELECT CONVERT_FROM(data, 'utf8')::jsonb AS data FROM json_source);The new way
CREATE SOURCE json_source
FROM KAFKA CONNECTION kafka_connection (TOPIC 'ch_anges')
FORMAT JSON
WITH (SIZE = '3xsmall');
CREATE VIEW extract_json_source AS
SELECT
(data->>'field1')::boolean AS field_1,
(data->>'field2')::int AS field_2,
(data->>'field3')::float AS field_3
-- This is slightly more fun!
FROM json_source;The FORMAT JSON option is already supported in the source creation UI,
and will land in the upcoming v0.1.0 release of the Terraform provider.
In the future, we plan to support automatically enforcing JSON schemas, both for
user-provided schemas and schemas managed via Confluent Schema Registry.