Unable to Parse MongoDB JSON with Arrays

Highlighted
Five Stars

Unable to Parse MongoDB JSON with Arrays

I get the below JSON as response to my Aggregation Query in tMongoInput.

I'm not able to access the values in the empl array, Can anybody please help me with this.

{
"_id" : {"$oid" : "5baac277be17f463df8a3c0d"},
"emp_no" : 10001,
"Salary" : 88958,
"Start_Date" : {"$date" : "2002-06-22T05:00:00Z"},
"End_Date" : {"$date" : "9999-01-01T06:00:00Z"},
"empl" : [ {
"_id" : {"$oid" : "5baad12cbe17f45d9c6797f7"},
"emp_no" : 10001,
"dept" : "d005",
"Start_Date" : {"$date" : "1986-06-26T05:00:00Z"},
"End_Date" : {"$date" : "9999-01-01T06:00:00Z"} }
]
}

Thanks and Regards,

Apoorv Jain

Moderator

Re: Unable to Parse MongoDB JSON with Arrays

Hello,

Where does your JSON response come from? tRestClient? Have you tried to access values by using tExtractJsonField 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.
Five Stars

Re: Unable to Parse MongoDB JSON with Arrays

Hi Sabrina,JobScrShot.JPGJob Screenshot

I'm getting it through tMongoDBInput, Yes I've tried extracting it through extractJSON but its giving me error.

Please find below screenshot of my job.

 

I'm using $lookup aggregation stage in tMongoDBInput, which is nothing but Left Outer Join in terms of RDBMS, it returns me all the matched documents from the lookup table as an array which is empl in my case.

I'm able to parse through all the fields of Main table in tMongoDbInput, but because empl is an array of JSONS, I separate it out as a separate output using tMap.

I use tJavarow to add {"Response": at the start of the message and } at the end (as it looses) it while coming out of tMongoDBInput.

So after all this I have the below JSON: 

 

{"Response":[
{ "_id" : { "$oid" : "5baad12cbe17f45d9c6797f7"} ,
"emp_no" : 10001 ,
"dept" : "d005" ,
"Start_Date" : { "$date" : "1986-06-26T05:00:00.000Z"} ,
"End_Date" : { "$date" : "9999-01-01T06:00:00.000Z"}
}]}

Which I pass to tExtractJSONFields with below config:

extractJsonSCRShot.JPG

But I get below error:

Error on line 1 of document  : The content of elements must consist of well-formed character data or markup. Nested exception: The content of elements must consist of well-formed character data or markup.

 

I Hope I was able to clear all the doubts.

 

Thanks and Regards,

Apoorv Jain

 

Five Stars

Re: Unable to Parse MongoDB JSON with Arrays

@xdshi Any more insights on this?

Five Stars

Re: Unable to Parse MongoDB JSON with Arrays

Can anybody please help me with this??
Moderator

Re: Unable to Parse MongoDB JSON with Arrays

Hello,

Sorry for our silence. We do our best to answer as many posts as possible.

Is there any JSON fields that start with a number? If so, could you please try to put a tReplace in before the tExtractJSONFields?

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.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog

Talend Integration with Databricks

Take a look at this video about Talend Integration with Databricks

Watch Now