Upload JSON from S3 Bucket

ryan_cheney · March 20

Hello! I have a JSON file in an S3 bucket with sample data from MLS house listings. I am unsure of what settings I need to get this to successfully upload and am getting this error:

A JSONObject text must end with '}' at 1 [character 2 line 1]

For reference, here is a sample of the JSON file:
{
  "1984933": {
    "ListingKeyNumeric": 1984933,
    "ListingStatus": "Active",
    "ModificationTimestamp": "2024-03-08T07:32:57Z",
    "PropertyType": "Residential",
    "PropertySubType": "Single Family Residence",
    "CurrentUse": "Single Family",
    "Ownership": "",
    "ArchitecturalStyle": "Bungalow/Cottage",
    "Stories": 1,
    "Basement": "None",
    "Address": "65 STRATFORD, South Salt Lake UT, 84115",
    "ListPrice": 398000.0,
    "OriginalListPrice": 398000.0,
    "ClosePrice": null,
    "CloseDate": null,
    "DaysOnMarket": 0,
    "CumulativeDaysOnMarket": null,
    "LivingAreaSquareFeet": 931.0,
    "LotSizeAcres": 0.1,
    "YearBuilt": 1925,
    "YearBuiltEffective": null,
    "BedroomsTotal": 2,
    "BathroomsTotal": 1,
    "GarageSpaces": null,
    "Cooling": true,
    "Heating": true,
    "ElementarySchool": "Woodstock",
    "MiddleOrJuniorSchool": "Granite Park",
    "HighSchool": "Cottonwood",
    "LeaseAmount": null,
    "NumberOfUnitsLeased": null,
    "NumberOfUnitsTotal": null,
    "ListingAgent": "Christine Sadira",
    "ListAgentMlsId": "88753",
    "ListingOfficeName": "Unity Group Real Estate LLC",
    "ListOfficeMlsId": "71188",
    "ExteriorFeatures": "",
    "AppliancesIncluded": "Dryer,Refrigerator,Washer",
    "TaxAnnualAmount": 1888.0,
    "AssociationFeeAnnual": null,
    "AssociationAmenities": "",
    "Topography": "Fenced: Full, Sidewalks, Sprinkler: Auto-Full",
    "LotFeatures": "Fenced: Full,Sidewalks,Sprinkler: Auto-Full",
    "Utilities": "",
    "Vegetation": "",
    "View": "",
    "OriginatingSystemName": "UtahRealEstate.com",
    "OriginatingSystemID": "M00000628"
  },
  "1984930": {
    "ListingKeyNumeric": 1984930,
    "ListingStatus": "Active",
    "ModificationTimestamp": "2024-03-08T07:32:57Z",

etc...

ArborRose · March 22

It looks like the JSON data structure has nested objects with unique keys. When pulling the data into Domo, each unique key is being treated as a separate column, which leads to too many columns.

ArborRose · March 20

The error indicates an extra character was found at the beginning of the file. Something wrong with the JSON structure. But the portion you posted looks correct.

When I code my own applications with JSON calls, I something find I need the outside wrapper and sometimes I don't. You can use online JSON validators to ensure the format is correct. (Be careful placing your information in other websites. Make sure there's no protected private information).

ArborRose · March 20

Taking the JSON you presented…if it were listed as a single record for 1984933, it should terminate something with parenthesis at the end like this…

{ "1984933": { "ListingKeyNumeric": 1984933, "ListingStatus": "Active", "ModificationTimestamp": "2024-03-08T07:32:57Z", "PropertyType": "Residential", "PropertySubType": "Single Family Residence", "CurrentUse": "Single Family", "Ownership": "", "ArchitecturalStyle": "Bungalow/Cottage", "Stories": 1, "Basement": "None", "Address": "65 STRATFORD, South Salt Lake UT, 84115", "ListPrice": 398000.0, "OriginalListPrice": 398000.0, "ClosePrice": null, "CloseDate": null, "DaysOnMarket": 0, "CumulativeDaysOnMarket": null, "LivingAreaSquareFeet": 931.0, "LotSizeAcres": 0.1, "YearBuilt": 1925, "YearBuiltEffective": null, "BedroomsTotal": 2, "BathroomsTotal": 1, "GarageSpaces": null, "Cooling": true, "Heating": true, "ElementarySchool": "Woodstock", "MiddleOrJuniorSchool": "Granite Park", "HighSchool": "Cottonwood", "LeaseAmount": null, "NumberOfUnitsLeased": null, "NumberOfUnitsTotal": null, "ListingAgent": "Christine Sadira", "ListAgentMlsId": "88753", "ListingOfficeName": "Unity Group Real Estate LLC", "ListOfficeMlsId": "71188", "ExteriorFeatures": "", "AppliancesIncluded": "Dryer,Refrigerator,Washer", "TaxAnnualAmount": 1888.0, "AssociationFeeAnnual": null, "AssociationAmenities": "", "Topography": "Fenced: Full, Sidewalks, Sprinkler: Auto-Full", "LotFeatures": "Fenced: Full,Sidewalks,Sprinkler: Auto-Full", "Utilities": "", "Vegetation": "", "View": "", "OriginatingSystemName": "UtahRealEstate.com", "OriginatingSystemID": "M00000628" }}

ryan_cheney · March 20

Thanks for the help. It could be that I was selecting yes to needing "readlines". Using the default JSON setting for the S3 connector, I am getting a new error:

'Domo system error encountered processing your data. The number of columns exceeds the allowed limit.'

Given the above sample I provided, there should be 48 columns. Is this actually over the limit or is the JSON not being processed correctly?

ArborRose · March 20

Based on the error, it sounds like the data includes too many columns. But I think the number of columns may vary based on the Domo plan or configuration. It may be that there are other reasons contributing to the issue. Size of the dataset, complexity of the JSON, etc. I would suggest testing with a subset of the data to see if the issue still occurs.

ryan_cheney · March 22

I took your advice and tried this with a subset. It appears that it is creating a new column for each field under a unique key. Here is a picture for reference:

Screenshot 2024-03-22 at 9.51.46 AM.png

ArborRose · March 22

It looks like the JSON data structure has nested objects with unique keys. When pulling the data into Domo, each unique key is being treated as a separate column, which leads to too many columns.

Upload JSON from S3 Bucket

Best Answer

Answers

Categories