[HOW TO?] insert json array into postgreSQL

Four Stars

[HOW TO?] insert json array into postgreSQL

Good afternoon ! 


I haven't been able to insert json array into a column postgresql database.


This is my job:


File : Job


What do i want?


I want to migrate data from a Mongo DB to PostgreSQL. I'm extracting data from my mongodb, using mongodbinput , and then i'm parsing the array because it comes with a weird syntax, like : [Document({"example":"example})] So the only thing i'm doing is to have this output:

[{"level":"1"," credits_per_hour":"2"," description":"hello"," _id":"571a02a8b4da67a67a744585"}','{"level":"2"," credits_per_hour":"1.5"," description":"hello"," _id":"571a02a8b4da67a67a744584"}','{"level":"3"," credits_per_hour":"1"," description":"hello"," _id":"571a02a8b4da67a67a744583"}]  


Attention: this is type TEXT!


After this I want to insert into my data base that data. 


Schema of database:


File: Schema


At additional options, i'm converting the string to an json array. See the image below:




However, it throws me an error : 

Detail: "[" must introduce explicitly-specified array dimensions. 


I can't understand this message... And i have been around this for like 4 days, so it would be nice some help Smiley Very Happy 


Hope you can help me,


Thanks on advance!


Re: [HOW TO?] insert json array into postgreSQL


Are you able to access values by using tExtractJsonFiled component?

Best regards


Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: [HOW TO?] insert json array into postgreSQL

Hi Sabrina,


thanks for the replay.



yes, I think so! I don't know the values are not being inserted into the database.


Re: [HOW TO?] insert json array into postgreSQL



    Unfortunately, I am not able to see the inline diagrams and they seem to be not accessible. Could you please add them again for reference?


    Also, could you please add a tlogrow before sending the data to database to see the output JSON you are getting? 


Warm Regards,


Nikhil Thampi