Unable to Parse MongoDB JSON with Arrays

[Anonymous]

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

[Anonymous]

Hello,

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

Best regards

Sabrina

[Anonymous]

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

[Anonymous]

@xdshi Any more insights on this?

[Anonymous]

Can anybody please help me with this??

[Anonymous]

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

[kanuparthy]

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