topic Re: Unable to Parse MongoDB JSON with Arrays in Talend Studio

Unable to Parse MongoDB JSON with Arrays

Anonymous — Sat, 16 Nov 2024 07:34:30 GMT

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

Re: Unable to Parse MongoDB JSON with Arrays

Anonymous — Fri, 28 Sep 2018 08:29:11 GMT

Hello,

Where does your JSON response come from? tRestClient? Have you tried to access values by using tExtractJsonField component?

Best regards

Sabrina

Re: Unable to Parse MongoDB JSON with Arrays

Anonymous — Fri, 28 Sep 2018 16:47:23 GMT

Hi Sabrina,Job 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:

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

Re: Unable to Parse MongoDB JSON with Arrays

Anonymous — Mon, 01 Oct 2018 17:21:57 GMT

@xdshi Any more insights on this?

Re: Unable to Parse MongoDB JSON with Arrays

Anonymous — Wed, 03 Oct 2018 22:29:38 GMT

Can anybody please help me with this??

Re: Unable to Parse MongoDB JSON with Arrays

Anonymous — Tue, 06 Nov 2018 06:38:39 GMT

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

Re: Unable to Parse MongoDB JSON with Arrays

kanuparthy — Fri, 17 Apr 2020 08:53:12 GMT

Can you let me know how to represent array in parent node on mongodboutput component.

I want output has mentoined below

{
"_id" : ObjectId("5e995cd93e586c1b302cfbd3"),
"claimId" : 7142,
"patientId" : 88229,
"patientLastName" : "TEST",
"patientFirstName" : "GARY",
"patientMiddleInitial" : "A",
"details" :[

{"patientSex" : "MALE"
}

],
"patientDob" : "1950-04-13"
}

Array.PNG