Loading jsonb datatype from postgres source to postgres target

[vabclb]

We have Amazon Aurora postgres db with a column datatype jsonb, when we are trying to run a full load only task and let create Qlik create target table with datatype jsonb, the task is failing with the following error:

[TARGET_LOAD ]E: Command failed to load data with exit error code 1, Command output: ERROR: invalid input syntax for type json.

Is there anykind of manipulation that Qlik is doing while trying to read and load data ? Because source and target datatype are same, so if data is present in any format on source, it should be loaded on target? 

[john_wang]

Hello @vabclb ,

I'd reproduced the behavior in my labs. please allow me sometime, I will get back to you with my findings shortly.

Regards,

John.

[john_wang · Accepted solution]

Hello @vabclb ,

The root cause of the error message "invalid input syntax for type json" is that the JSONB column was truncated, resulting in incomplete and invalid JSON documents. This led to the error.

To resolve this issue, follow these steps:

1. Adjust the task settings

   • Set "Limit LOB size to" to an appropriate value, e.g., 1024.

   • Ensure this value accommodates the maximum expected size of your JSONB column.

2. Verify the task logs

   • Check the task log file to confirm that the JSONB column is not being truncated.

   • If truncation occurs, you may see a warning message similar to the following:

       2025-03-27T11:43:48:185756 [SOURCE_UNLOAD ]W: Truncation of a column occurred while fetching a value from array (for more details please use verbose logs) (ar_odbc_stmt.c:3485)

Hope this helps.

John.

[DesmondWOO · Accepted solution]

Hi @vabclb ,

For additional information, JSONB data is treated as a wide string, which is 2 bytes long. Therefore, you need to double the limited LOB size.

Regards,
Desmond

[vabclb]

Thanks a lot for inputs, we'll try to add verbose logging and see if its truncation and will adjust the LOB size that qlik task need to handle. Will respond when we have found a working solution.

[john_wang]

Thank you so much for your support! @vabclb 

[vabclb]

Update, it was truncation issue and once we updated the LOB size to 100kb, it worked fine, thanks for your prompt support.

[john_wang]

Thanks for the update. We really appreciate your support! @vabclb 

[vabclb]

Follow up question, in the task settings > table transformations, the datatype for the source jsonb field is coming as NCLOB, I updated the subtype to JSON, is that required or Regular will work too? Thanks

[john_wang]

Hello @vabclb ,

The subtype is not applicable to PostgreSQL endpoints. You may ignore it here. The Regular works always.

thanks,

John.