How to decode encoded data read by 'captureChangeMySql' processor

[#1]

Hi ,

I want to move source DB data to destination DB. Source db table has  'metadat' column, dataType='JSON'. I am using 'captureChangeMySql' processor to ready the change data from  mysql binary-log file and put a data to destination db.

'captureChangeMySql' processor to reading the change data from  mysql binary-log file but 'metadat' column, dataType='JSON' , value is showing as some encoded format.

Can anyone suggest, how to decode, encoded data read by 'captureChangeMySql' processor flowfile output using any other NiFi processor?

Original data in db: 

{"mappedProducts": ["20-4040", "20-4041", "20-4042", "20-4043"]}

Flowfile output:

[ {
"id" : 18,
"name" : "Transitional Licenses - Provider",
"external_id" : "101",
"product_tag" : null,
"tenant_id" : 1,
"metadata" : "\u0000\u0001\u0000I\u0000\u000B\u0000\u000E\u0000\u0002\u0019\u0000mappedProducts\u0004\u00000\u0000\f\u0010\u0000\f\u0018\u0000\f \u0000\f(\u0000\u000720-4040\u000720-4041\u000720-4042\u000720-4043",
"duration_in_days" : null,
"offline_completion_duration" : null,
"product_code" : "cps_provider_7_ed",
"external_code" : null,
"external_url" : null,
"short_description" : "This course is for Providers",
"long_description" : "This course is for Providers",
"meta_keyword" : null,
"meta_description" : null,
"meta_title" : null,
"status" : "Active",
"event_type" : "ESSENTIALS",
"event_participation_type" : "REGISTER_ILE",
"terms" : null,
"product_type_id" : 1,
"is_perpetual" : null,
"sort_order" : "20",
"transitional_sort_order" : null,
"completion_cert_type" : null,
"ecard_cert_type" : null,
"ecard_validity" : null,
"ecard_name" : null,
"is_deleted" : "{}",
"created_at" : "Thu Mar 04 13:15:53 IST 2021",
"updated_at" : "Thu Mar 04 13:15:53 IST 2021",
"created_by" : null,
"updated_by" : null,
"is_legacy" : "{0}",
"has_ce" : "{}",
"region" : "CA",
"self_registration_override" : "{}",
"allow_assignment" : "{0}",
"default_license_type" : null,
"dispatch_confirmation" : "{}",
"multi_package" : "{}"
} ]

[#2]

@AbhishekSingh ,

The JSON datatype in MySQL is stored in an optimized binary format. When you do CDC you only get the binary changes associated with that column. If you are processing this within MySQL, you have functions to deal with the JSON datatype. In NiFi, though, I'm afraid this is not possible.

If you want to get the JSON changes as text you should convert the column data type to a string-type.

Cheers,

André

[#3]

@araujo 

Changing a datatype of source DB in production environment will not be a good solution.

Is there any other approach to handle Mysql  JSON datatype in NiFi ready from Mysql binarylog?

Thanks,

[#4]

I don't think there is. At least, I can't think of one.