[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:

 

File:Convert_to_array

 

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!


Moderator

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

Hello,

Are you able to access values by using tExtractJsonFiled component?

Best regards

Sabrina

--
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.

Employee

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

Hi,

 

    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