Hi, I’m a new user of Meltano and I’m struggling with getting a pipeline to work. I’m trying to replicate a JSON column in MySQL 8.0.33 to Postgres. The problem seems to be that validation fails because the JSON type is being extracted as a string:

jsonschema.exceptions.ValidationError: '{"id": "1234", "date": "2020-04-08T00:00:00", "name": "name", }' is not of type 'null', 'object'

When I invoke the tap-mysql on its own, I can see that the JSON column is indeed stored as a string. I believe that this is the same issue as: https://github.com/transferwise/pipelinewise-tap-mysql/issues/99 and https://github.com/transferwise/pipelinewise-tap-mysql/issues/165 although the MySQL version is different. Is there a workaround anyone can suggest please?

Hi @benjamin_sims! You can probably extractor the schema setting to override the field type:

extractors:
- name: tap-mysql
  schema:
    my_table_name:
      my_column_name:
        type: ["string", "null"]

Thanks - I tried adding that to both the extractor and the loader, but it doesn’t seem to have any effect. This sounds somewhat similar to: https://github.com/transferwise/pipelinewise-tap-postgres/issues/128 (although the tap is MySQL and the target is using the meltanolabs variant)

Hi Benjamin. Which target and variant are you using?

Hi, I’m using target-postgres, meltanolabs, 0.0.7

I asked in #C04CLA0S4G0. In the meantime https://hub.meltano.com/loaders/target-postgres--transferwise/ does support column alteration so the schema override in

meltano.yml

should work. Note that the config (and maybe the state) of the variants are not completely compatible.

Thanks - I may be wrong but it feels like the problem isn’t really about the schema override, but the differing treatments of the formats. As in, we don’t want JSON to be transformed to a string - it’s a JSON column in both database and should replicate as such. I think the tap and the target have different approaches to serialisation / deserialisation, hence why my JQ fix works

I see. Then yeah, other than your jq workaround or fixing this in the tap, another option would be for us to implement https://github.com/meltano/sdk/issues/1502 so you could use the map transformer to convert to deserialize the string on the fly.