MySQL JSON data type is converted to string

695 views

Skip to first unread message

Ramu N

unread,

Dec 13, 2018, 6:39:40 AM12/13/18

to debezium

Hi,

As mentioned in https://debezium.io/docs/connectors/mysql/#data-types, MySQL JSON data type is mapped string data type of kafka connector.

but i want to receive JSON data without converting to string, is there any way to do it ?

schema section in the event :

{

"type": "string",

"optional": true,

"name": "io.debezium.data.Json",

"version": 1,

"field": "params"

{

"type": "string",

"optional": true,

"name": "io.debezium.data.Json",

"version": 1,

"field": "labels"

}

payload section in the event :

{

"params": "[{\"desc\":\"aad\",\"name\":\"para\",\"type\":\"String\",\"defaultVal\":\"pra\"},{\"desc\":\"Array\",\"name\":\"testing\",\"type\":\"Object\",\"attributes\":\"yrs,uhgl\"}]",

"labels": "[{\"value\":\"asfsf\",\"dimension\":\"saf\"}]"

}

Expected payload:

{

"params": [{"desc":"aad","name":"para","type":"String","defaultVal":"pra"},{"desc":"Array","name":"testing","type":"Object","attributes":"yrs,uhgl"}],

"labels": [{"value":"asfsf","dimension":"saf"}]

}

Thanks,

Ramu N

Gunnar Morling

unread,

Dec 13, 2018, 3:33:19 PM12/13/18

to debezium

It's not possible out of the box. The fact that you omitted the expected schema hints into the right direction what's the problem :)

We'd have to dynamically create a schema for each emitted message (as the structure of the JSON field can be different each time), which would potentially create lots of schema versions when using the Avro schema registry.

One way to address your requirement is to use a message transformation (SMT) which takes the JSON and expands it into a typed Kafka Connect structure. You can take our MongoDB unwrapping SMT (https://github.com/debezium/debezium/blob/master/debezium-connector-mongodb/src/main/java/io/debezium/connector/mongodb/transforms/UnwrapFromMongoDbEnvelope.java) as starting point for such implementation, as it's doing essentially the same thing.