Hi, I'm using FME to parse a JSON file and eventually upload specific parts to an Oracle table.
One concept I'm not sure how to approach is, what to do when the JSON has a nested list, that is always a different size?
Would the JSONFragmenter be the best option in this case, or something like the JSONExtractor?
When using the JSONFeature reader, I can only get one item of the list at a time.
I've attached a snapshot of how the section of the JSON looks.
+32
I think this depends mostly on the structure of the target system, where you want to load the data. In your case, the Oracle table. If you have a table "Uploaded Documents" with a record for each uploaded document then you probably want to fragment the list to features, which you write as records in Oracle.
I've been working with the JSONFragmenter feature to fragment the list, but have had little luck.
I think it's the query itself or the 'Attributes to Expose' section that could be messing up my results (attached photo).
Whenever I run it, I get Total Features Read as 0, and no errors.
It could be that it is running fine, but I'm not specifying where to fetch the data from properly.
Any thoughts?
+32
I've been working with the JSONFragmenter feature to fragment the list, but have had little luck.
I think it's the query itself or the 'Attributes to Expose' section that could be messing up my results (attached photo).
Whenever I run it, I get Total Features Read as 0, and no errors.
It could be that it is running fine, but I'm not specifying where to fetch the data from properly.
Any thoughts?
Hard to say without a sample to reproduce, but I think this can work.
Did you generate the query by clicking the "browse" button and select the element in the tree?
You also can work in two steps, first fragmenting json["UserInfoList"][*] then using a second fragmenter fragment json["UploadedDocumentList"][*] but it all depends on the structure of the file and what you want / need.
Hard to say without a sample to reproduce, but I think this can work.
Did you generate the query by clicking the "browse" button and select the element in the tree?
You also can work in two steps, first fragmenting json["UserInfoList"][*] then using a second fragmenter fragment json["UploadedDocumentList"][*] but it all depends on the structure of the file and what you want / need.
EDIT: I believe I've figured this out.
What worked was adding a Creator transformer first, along with each individual attribute I'd like to expose in said section of the JSONFragmenter, and then using an AttributeRemover to remove '_result,json_index,json_type'.
Previous Question:
I did generate the query via the "browse" button, and also tried fragmenting into 2 separate fragmenters, but keep getting 0 results.
I will attach a screenshot of the original log/layout, and also provide the JSON file text.
I could just be misinterpreting the log and inspector readouts.
JSON:
{
"UserInfoList": [
{
"IntegrationUserID": "24892",
"UserProfile": {
"FirstName": "Steve",
"MiddleName": "",
"LastName": "Stevenson",
"EnglishName": "",
"PrimaryEmail": "stevestevenson@sample.com",
"SecondaryEmail": "stevestevenson@gmail.com",
"PrimaryPhoneNumber": "",
"SecondaryPhoneNumber": "",
"DOB": "1900-03-19",
"Gender": "M",
"CitizenshipCountryCode": "FI",
"CitizenshipCountryName": "Finland",
"UserProfileCreatedOnUTC": "2020-Jul-10 12:00 AM",
"UserProfileUpdatedOnUTC": "2020-Jul-22 01:30 PM"
},
"ProgramProfile": {
"UserCategory": "STUDENT",
"CampusCode": "",
"CampusName": "",
"ProgramCode": "",
"ProgramName": "",
"AcademicSchoolCode": "",
"AcademicSchoolName": "",
"AcademicLevelCode": "",
"AcademicLevel": "",
"CoopInternship": false,
"AdmitTerm": "Fall2021",
"SemesterCode": "Sem3",
"Semester": "Semester 3",
"ProgramProfileCreatedOnUTC": "2020-Jul-20 11:23 PM",
"ProgramProfileUpdatedOnUTC": "2020-Jul-24 01:50 PM"
},
"MailingAddressList": [
{
"AddressSequence": "1",
"AddressFromDate": "2019-Mar-20",
"AddressToDate": "2020-Mar-19",
"AddressTypeCode": "",
"AddressType": "",
"AddressLine1": "",
"AddressLine2": "",
"CountyCode": "",
"CountyName": "",
"CountryCode": "",
"CountryName": "",
"StateProvinceCode": "",
"StateProvinceName": "",
"ZipPostalCode": "",
"City": "",
"MailingAddressCreatedOnUTC": "2019-Mar-24 03:10 PM",
"MailingAddressUpdatedOnUTC": "2019-Apr-16 04:25 PM"
}
],
"EmergencyContact": {
"EcFirstName": "",
"EcLastName": "",
"EcRelationship": "",
"EcEmail": "",
"EcPrimaryPhoneNumber": "",
"EcSecondaryPhoneNumber": "",
"EcPrimaryLanguageSpoken": "",
"EcSecondaryLanguageSpoken": "",
"EcAddress": "",
"EcCountryCode": "",
"EcCountryName": "",
"EcStateProvinceCode": "",
"EcStateProvinceName": "",
"EcZipPostalCode": "",
"EmergencyContactUpdatedOnUTC": "2020-Jul-26 04:00 PM"
},
"UploadedDocumentList": [
{
"DocumentID": "10",
"DocumentDisplayName": "Sample Study Permit",
"DocumentPath": "https://{domainname}/sample.png",
"DocumentType": "Study Permit",
"DocumentTypeCode": "StudyPermit",
"DocumentSubmittedOnUTC": "2020-Jul-29 08:30 PM"
},
{
"DocumentID": "11",
"DocumentDisplayName": "Sample visa document",
"DocumentPath": "https://{domainname}/sample2.png",
"DocumentType": "Visa Document",
"DocumentTypeCode": "Visa",
"DocumentSubmittedOnUTC": "2020-Jul-29 03:30 PM"
}
]
}
]
}
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.