Skip to main content
  • EN

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.

 

Annotation 2021-03-29 165729

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

 

Annotation 2021-04-01 115520 

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?

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

 

Annotation 2021-04-01 115520 

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 jsons"UserInfoList"]

  • then using a second fragmenter fragment jsons"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.Annotation 2021-04-05 172024 

    JSON:

    {	
      "UserInfoList": c	
        {	
          "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"	
            }	
          ]	
        }	
      ]	
    }

     

    Reply


    Community Stats

    30,179
    Posts
    114,557
    Replies
    37,844
    Members
    Terms & ConditionsCookie settings
    A product of
    Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.