Changelog Kafka source: improved JSON support
Changelog

Kafka source: improved JSON support

07.07.2023

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

1 
CREATE SOURCE json_source
2 
  FROM KAFKA CONNECTION kafka_connection (TOPIC 'ch_anges')
3 
  FORMAT BYTES
4 
  WITH (SIZE = '3xsmall');
5
6 
CREATE VIEW extract_json_source AS
7 
  SELECT
8 
    (data->>'field1')::boolean AS field_1,
9 
    (data->>'field2')::int AS field_2,
10 
    (data->>'field3')::float AS field_3
11 
  -- This is not fun
12 
  FROM (SELECT CONVERT_FROM(data, 'utf8')::jsonb AS data FROM json_source);
sql

The new way

1 
CREATE SOURCE json_source
2 
  FROM KAFKA CONNECTION kafka_connection (TOPIC 'ch_anges')
3 
  FORMAT JSON
4 
  WITH (SIZE = '3xsmall');
5
6 
CREATE VIEW extract_json_source AS
7 
  SELECT
8 
    (data->>'field1')::boolean AS field_1,
9 
    (data->>'field2')::int AS field_2,
10 
    (data->>'field3')::float AS field_3
11 
  -- This is slightly more fun!
12 
  FROM json_source;
sql

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.