invalid input syntax for type json

[Ardodia]

Hello,
When migrating oracle data to Postgres I got this error with ora2pg

ERROR (log error enabled): ERROR: invalid input syntax for type json
DETAIL: The input string ended unexpectedly.
CONTEXT: JSON data, line 1: ..."5670","5651","5630","5652","5631","5653","5643",
COPY test, line 2, column complementjson: "{"attributMaitre":"$a$********","valeursFiltrage":{"22":["2210","2220"],"23":["2310","2320","2385"],"..."

Here is the structure of the Oracle table.

**CREATE TABLE TEST
( IDXX VARCHAR2(25 BYTE),
COMPLEMENTXXX CLOB
CONSTRAINT "COMPLEMENTJSON_JSON_CHECK" CHECK (COMPLEMENTXXX IS JSON) ENABLE
)

TABLESPACE "XXXXX"
LOB ("COMPLEMENTXXX") STORE AS SECUREFILE (
TABLESPACE "xxxxx" ENABLE STORAGE IN ROW CHUNK )**

The column COMPLEMENTXXX contains this row

{"attributMaitre":"$a$********","valeursFiltrage":{"22":["2210","2220"],"23":["2310","2320","2385"],"24":["2400"],"69":["6901"],"27":["2700"],"29":["2900"],"91":["9150","9110"],"92":["9223","9224","9210","9221","9222","9260","9230","9220","9240"],"71":["7120","7112","7111","7113","7179","7190","7150","7172","7160","7171"],"93":["9300"],"72":["7220","7230","7210","7225","7229"],"51":["5195","5196","5191","5192","5193","5194"],"73":["7341","7363","7385","7340","7362","7384","7321","7343","7365","7342","7364","7323","7345","7367","7389","7322","7344","7366","7347","7346","7381","7361","7383","7382","7352","7351","7373","7354","7331","7353","7312","7356","7378","7355","7314","7313","7357","7379","7372","7371","7349","7348"],"52":["5202","5203"],"74":["7450","7430","7410","7490","7470"],"31":["3110","3120"],"53":["5385","5306","5307","5308","5309","5370"],"32":["3290","3205","3210","3220"],"54":["5470","5460","5451","5430","5485","5431","5442","5453","5410","5432","5443","5454","5498","5422","5455","5499","5458","5415","5426","5459"],"11":["1100"],"55":["5560","5585","5520","5542","5543","5555","5599","5558","5515","5559","5570","5551","5530","5552","5531","5553","5510","5532","5554","5522","5546","5525","5547","5548","5505"],"99":["9970","9900"],"12":["1200"],"56":["5660","5685","5620","5642","5610","5632","5654","5655","5699","5658","5615","5659","5670","5651","5630","5652","5631","5653","5643",

"5622","5646","5625","5647","5648","5605"],"13":["1300"],"57":["5770","5785","5720","5710"],"14":["1400"],"58":["5800"],"15":["1500"],"16":["1600"],"17":["1700"],"18":["1800"],"19":["1900"],"81":["8110","8170","8160","8190","8130","8120","8150","8140"],"82":["8210","8290","8250"],"61":["6100"],"83":["8310","8311"],"62":["6210","6220"],"84":["8420","8410","8490","8450","8470"],"41":["4150",
"4140","4130","4120","4110","4160"],"63":["6317","6316","6318"],"85":["8520","8510"],"64":["6411"],"21":["2110","2120"],"65":["6571","6551","6573","6595","6572","6575","6597","6574","6596","6511","6533","6577","6599","6532","6554","6576","6598","6568","6567","6589","6569","6560","6540","6562","6561","6542","6564","6541","6563","6585","6544","6566","6588","6521","6543","6565","6535","6534","6578","6537","6536","6558","6539","6538"]}}

In the ora2pg config file, we have:

MODIFY_TYPE TEST:COMPLEMENTXXX:JSON

The structure of the table in Postgres.

**create table test
(
idxx character varying(25)

complementjson json

)**

Thank you in advance.

[darold]

Json doesn't allow newline character, if you remove it data are well inserted.

You can use TRANSFORM_VALUE or REPLACE_QUERY for that.