JSON Array to PostgreSQL Array error

[tristanbsn]

When extracting data from JSON file and loading into PostgreSQL database, array values are not handled correctly.

For exemple when trying to load value ["value1", "value2"] from JSON file into an varchar[] field in PostgreSQL database, this error occure :

Array to string conversion

The JSON array should be transformed in PostgreSQL Array format string so it can be properly inserted in database : "{value1, value2}".

[ecourtial]

Hi :)

Do you have a sample of the original data and what should be inserted to the DB?
Any further information for a quick fix would be welcome :)

[tristanbsn]

Hi,

Here is the source JSON file :
[ { "refsystemflag": [ "synch:20210117054646Z" ], "objectclass": [ "refUser", "refSystemInfo" ], "postalcode": "69622", "refunauthenticable": "TRUE", "createtimestamp": "20160223170538Z", "modifytimestamp": "20210117054646Z", "c": "FR", "refressourceetat": [ "{COMPTE}S:VerrouTechnique", "TestValue" ], "departmentnumber": "[DS=53][DR=07][DI=07][U=UMR5310][I=1]", "_internal": { "insertedOn": "2016-03-11T11:51:49.689+01:00", "updatedOn": "2022-01-05T12:39:53.944383+01:00", "sha1sum": "fc812976528e4d825b783d2ebb8f2c978040b72d", "eventNumber": 3418557, "deletedOn": "2022-01-05T12:39:53.944383+01:00" } } ]

The field "refressourceetat" value is an array of string values that need to be inserted in a varchar[] field in the PostgreSQL database. To insert an array in PostgreSQL, the value should be a string composed of the array values surrounded by "{" and "}" symbols, so in my case : {\{COMPTE\}S:VerrouTechnique, TestValue} (notice the use of "\" character to escape "{" and "}" in the first value of the array).

[ecourtial]

OK I will look at it ASAP.

[ecourtial]

I looked quickly, and I don't see any code, but the connectors of course, specific to any database system.

The library only provides connectors and logic (in the loaders). I don't see any code dealing with transformation to the good format for a specific given database engine.

I would suggest that you perform this data transformation into a transformer. That's the role of this step.

[tristanbsn]

Thank you for your answer, I managed to make it work with a transformer.