JSON to CSV flattening nested JSON
If you are looking for the fix to your actual code then you need to use something like below
function flatten(object, addToList, prefix) {
Object.keys(object).map(key => {
if (object[key] === null) {
addToList[prefix + key] = "";
} else
if (object[key] instanceof Array) {
// addToList[prefix + key] = listToFlatString(object[key]);
for (i in object[key]) {
flatten(object[key][i], addToList, prefix + key + "." + i)
}
} else if (typeof object[key] == 'object' && !object[key].toLocaleDateString) {
flatten(object[key], addToList, prefix + key + '.');
} else {
addToList[prefix + key] = object[key];
}
});
return addToList;
}
I replace
addToList[prefix + key] = listToFlatString(object[key]);
with
for (i in object[key]) {
flatten(object[key][i], addToList, prefix + key + "." + i)
}
That fixed the issue and the output is below as per the sample json you provided
[ '0.countyCode': 12,
'0.excludedFlag': '',
'0.fees.annualTotalFees': 35.6,
'0.fees.annualTotalFeesMail': 36.35,
'0.fees.annualTotalFeesOnline': 38.35,
'0.fees.biennialTotalFees': 71.2,
'0.fees.biennialTotalFeesMail': 71.95,
'0.fees.biennialTotalFeesOnline': 73.95,
'0.fees.branchFeeFlag': false,
'0.fees.delinquentFeeAmount': 5,
'0.fees.mhBackTax': 0,
'0.fees.mhBackTaxMonths': 0,
'0.fileId': 522,
'0.id': '0002be04-546-4a92-a3d7-c31546544f',
'0.messages.0messageCode': 'RN2',
'0.messages.0messageText': 'Plate must be replaced if a 2 year renewal is desired.',
'0.messages.0messageType': 'I',
'0.messages.1messageCode': 'RN40',
'0.messages.1messageText': 'You must complete the affidavit on the reverse side or provide a copy of your Florida insurance identification card.',
'0.messages.1messageType': 'I',
'0.messages.2messageCode': 'RN41',
'0.messages.2messageText': 'Insurance is on file.',
'0.messages.2messageType': 'II',
'0.registrationDetail.annualPlateReplacementFlag': false,
'0.registrationDetail.arfCredit': 25.6,
'0.registrationDetail.biennialPlateReplacementFlag': true,
'0.registrationDetail.customerStopFlag': '',
'0.registrationDetail.delinquentDate': '2018-02-11T00:00:00',
'0.registrationDetail.expirationDate': '2018-01-09T00:00:00',
'0.registrationDetail.foreignAddressFlag': false,
'0.registrationDetail.honorayConsulPlateFlag': '',
'0.registrationDetail.hovDecalNumber': '',
'0.registrationDetail.hovDecalYear': '',
'0.registrationDetail.hovExpirationDate': '',
'0.registrationDetail.inventorySubtype': 'RP',
'0.registrationDetail.lastActivityCounty': 12,
'0.registrationDetail.legislativePlateDueForReplacementFlag': false,
'0.registrationDetail.licensePlateCode': 'RGR',
'0.registrationDetail.licensePlateNumber': 'L45656',
'0.registrationDetail.mailToAddressFlag': false,
'0.registrationDetail.mailToCustomerNumber': '',
'0.registrationDetail.mhLocationCode': '',
'0.registrationDetail.militaryOwnerFlag': false,
'0.registrationDetail.numberOfRegistrants': 1,
'0.registrationDetail.onlineRenewalEligibilityFlag': true,
'0.registrationDetail.pinNumber': 222354654,
'0.registrationDetail.plateExpirationDate': '2018-03-18T00:00:00',
'0.registrationDetail.plateIssueDate': '2008-09-18T00:00:00',
'0.registrationDetail.possibleNgExemptionFlag': false,
'0.registrationDetail.registrationNumber': 2234545345,
'0.registrationDetail.registrationOnlyFlag': '',
'0.registrationDetail.registrationType': 'R',
'0.registrationDetail.registrationUse': 'PR',
'0.registrationDetail.renewalCountyCode': 12,
'0.registrationDetail.rentalParkFlag': '',
'0.registrationDetail.seminoleMiccosukeeIndianFlag': false,
'0.registrationDetail.taxCollectorRenewalEligibilityFlag': true,
'0.registrationDetail.vehicleClassCode': 1,
'0.registrationOwners.0customer.addresses.0addressType': 'R',
'0.registrationOwners.0customer.addresses.0city': 'PALMETTO',
'0.registrationOwners.0customer.addresses.0countryCode': 'US',
'0.registrationOwners.0customer.addresses.0countyCode': 12,
'0.registrationOwners.0customer.addresses.0foreignPostalCode': '',
'0.registrationOwners.0customer.addresses.0state': 'FL',
'0.registrationOwners.0customer.addresses.0streetAddress': '34545 7TH AVE W',
'0.registrationOwners.0customer.addresses.0zipCode': 34221,
'0.registrationOwners.0customer.addresses.0zipPlus': '',
'0.registrationOwners.0customer.addresses.1addressType': 'M',
'0.registrationOwners.0customer.addresses.1city': 'PALMETTO',
'0.registrationOwners.0customer.addresses.1countryCode': 'US',
'0.registrationOwners.0customer.addresses.1countyCode': 12,
'0.registrationOwners.0customer.addresses.1foreignPostalCode': '',
'0.registrationOwners.0customer.addresses.1state': 'FL',
'0.registrationOwners.0customer.addresses.1streetAddress': '34545 7TH AVE W',
'0.registrationOwners.0customer.addresses.1zipCode': 34221,
'0.registrationOwners.0customer.addresses.1zipPlus': 3433,
'0.registrationOwners.0customer.companyName': '',
'0.registrationOwners.0customer.customerNumber': 3346645,
'0.registrationOwners.0customer.customerStopFlag': false,
'0.registrationOwners.0customer.customerType': 'I',
'0.registrationOwners.0customer.dateOfBirth': '1971-01-09T00:00:00',
'0.registrationOwners.0customer.dlExpirationDate': '2025-01-09T00:00:00',
'0.registrationOwners.0customer.dlRenewalEligibilityFlag': true,
'0.registrationOwners.0customer.driverLicenseNumber': 'F34545345345',
'0.registrationOwners.0customer.emailAddress': '',
'0.registrationOwners.0customer.feidNumber': '',
'0.registrationOwners.0customer.firstName': 'DAVID',
'0.registrationOwners.0customer.lastName': 'HUGH',
'0.registrationOwners.0customer.middleName': 'BRIERTON',
'0.registrationOwners.0customer.militaryExemptionFlag': false,
'0.registrationOwners.0customer.nameSuffix': '',
'0.registrationOwners.0customer.sex': 'F',
'0.registrationOwners.0registrationOwnershipNumber': 1,
'0.shippingAddress.address.addressType': 'S',
'0.shippingAddress.address.city': 'PALMETTO',
'0.shippingAddress.address.countryCode': 'US',
'0.shippingAddress.address.countyCode': 12,
'0.shippingAddress.address.foreignPostalCode': '',
'0.shippingAddress.address.state': 'FL',
'0.shippingAddress.address.streetAddress': '34545 7TH AVE W',
'0.shippingAddress.address.zipCode': 34221,
'0.shippingAddress.address.zipPlus': 8344,
'0.shippingAddress.shippingCompanyName': '',
'0.shippingAddress.shippingName1': 'DAVID HUGH BRIERTON',
'0.shippingAddress.shippingName2': '',
'0.stops': '',
'0.vehicle.address': '',
'0.vehicle.bodyCode': '4D',
'0.vehicle.brakeHorsePower': '',
'0.vehicle.cubicCentimeters': '',
'0.vehicle.grossWeight': '',
'0.vehicle.insuranceAffidavitCode': '',
'0.vehicle.leaseOwnerFlag': false,
'0.vehicle.lengthFeet': '',
'0.vehicle.lengthInches': '',
'0.vehicle.majorColorCode': 'GLD',
'0.vehicle.makeCode': 'CHEV',
'0.vehicle.minorColorCode': '',
'0.vehicle.netWeight': 3200,
'0.vehicle.numberOfAxles': '',
'0.vehicle.ownerUnitNumber': '',
'0.vehicle.titleNumber': 345454534,
'0.vehicle.vehicleIdentificationNumber': '1G1ZD345U5B345435',
'0.vehicle.vehicleNumber': 23454656,
'0.vehicle.vehicleType': 'AU',
'0.vehicle.vesselCode': '',
'0.vehicle.vesselManufacturerDesc': '',
'0.vehicle.vesselResidentStatus': 'Y',
'0.vehicle.vesselWaterType': '',
'0.vehicle.widthFeet': '',
'0.vehicle.widthInches': '',
'0.vehicle.yearMake': 2011,
'1.countyCode': 12,
'1.excludedFlag': '',
'1.fees.annualTotalFees': 27.6,
'1.fees.annualTotalFeesMail': 28.35,
'1.fees.annualTotalFeesOnline': 30.35,
'1.fees.biennialTotalFees': 55.2,
'1.fees.biennialTotalFeesMail': 55.95,
'1.fees.biennialTotalFeesOnline': 57.95,
'1.fees.branchFeeFlag': false,
'1.fees.delinquentFeeAmount': 5,
'1.fees.mhBackTax': 0,
'1.fees.mhBackTaxMonths': 0,
'1.fileId': 522,
'1.id': '0008c654-8960-45b8-b416-cff3456767',
'1.messages.0messageCode': 'RN40',
'1.messages.0messageText': 'You must complete the affidavit on the reverse side or provide a copy of your Florida insurance identification card.',
'1.messages.0messageType': 'I',
'1.registrationDetail.annualPlateReplacementFlag': false,
'1.registrationDetail.arfCredit': 2.8,
'1.registrationDetail.biennialPlateReplacementFlag': false,
'1.registrationDetail.customerStopFlag': '',
'1.registrationDetail.delinquentDate': '2018-02-11T00:00:00',
'1.registrationDetail.expirationDate': '2018-01-01T00:00:00',
'1.registrationDetail.foreignAddressFlag': false,
'1.registrationDetail.honorayConsulPlateFlag': '',
'1.registrationDetail.hovDecalNumber': '',
'1.registrationDetail.hovDecalYear': '',
'1.registrationDetail.hovExpirationDate': '',
'1.registrationDetail.inventorySubtype': 'SS',
'1.registrationDetail.lastActivityCounty': 16,
'1.registrationDetail.legislativePlateDueForReplacementFlag': false,
'1.registrationDetail.licensePlateCode': 'RGS',
'1.registrationDetail.licensePlateNumber': 'HU34598',
'1.registrationDetail.mailToAddressFlag': false,
'1.registrationDetail.mailToCustomerNumber': '',
'1.registrationDetail.mhLocationCode': '',
'1.registrationDetail.militaryOwnerFlag': false,
'1.registrationDetail.numberOfRegistrants': 1,
'1.registrationDetail.onlineRenewalEligibilityFlag': true,
'1.registrationDetail.pinNumber': 4936856,
'1.registrationDetail.plateExpirationDate': '2026-09-24T00:00:00',
'1.registrationDetail.plateIssueDate': '2017-03-24T00:00:00',
'1.registrationDetail.possibleNgExemptionFlag': false,
'1.registrationDetail.registrationNumber': 4095685,
'1.registrationDetail.registrationOnlyFlag': '',
'1.registrationDetail.registrationType': 'R',
'1.registrationDetail.registrationUse': 'PR',
'1.registrationDetail.renewalCountyCode': 12,
'1.registrationDetail.rentalParkFlag': '',
'1.registrationDetail.seminoleMiccosukeeIndianFlag': false,
'1.registrationDetail.taxCollectorRenewalEligibilityFlag': true,
'1.registrationDetail.vehicleClassCode': 1,
'1.registrationOwners.0customer.addresses.0addressType': 'R',
'1.registrationOwners.0customer.addresses.0city': 'SARASOTA',
'1.registrationOwners.0customer.addresses.0countryCode': 'US',
'1.registrationOwners.0customer.addresses.0countyCode': 12,
'1.registrationOwners.0customer.addresses.0foreignPostalCode': '',
'1.registrationOwners.0customer.addresses.0state': 'FL',
'1.registrationOwners.0customer.addresses.0streetAddress': '5858 FRUITVILLE RD',
'1.registrationOwners.0customer.addresses.0zipCode': 34240,
'1.registrationOwners.0customer.addresses.0zipPlus': 5858,
'1.registrationOwners.0customer.addresses.1addressType': 'M',
'1.registrationOwners.0customer.addresses.1city': 'SARASOTA',
'1.registrationOwners.0customer.addresses.1countryCode': 'US',
'1.registrationOwners.0customer.addresses.1countyCode': 16,
'1.registrationOwners.0customer.addresses.1foreignPostalCode': '',
'1.registrationOwners.0customer.addresses.1state': 'FL',
'1.registrationOwners.0customer.addresses.1streetAddress': '5858 FRUITVILLE RD',
'1.registrationOwners.0customer.addresses.1zipCode': 34240,
'1.registrationOwners.0customer.addresses.1zipPlus': 5858,
'1.registrationOwners.0customer.companyName': '',
'1.registrationOwners.0customer.customerNumber': 2928357,
'1.registrationOwners.0customer.customerStopFlag': false,
'1.registrationOwners.0customer.customerType': 'I',
'1.registrationOwners.0customer.dateOfBirth': '1989-01-01T00:00:00',
'1.registrationOwners.0customer.dlExpirationDate': '2022-01-01T00:00:00',
'1.registrationOwners.0customer.dlRenewalEligibilityFlag': true,
'1.registrationOwners.0customer.driverLicenseNumber': 'B94832734',
'1.registrationOwners.0customer.emailAddress': '',
'1.registrationOwners.0customer.feidNumber': '',
'1.registrationOwners.0customer.firstName': 'DAVID1',
'1.registrationOwners.0customer.lastName': 'HUGH1',
'1.registrationOwners.0customer.middleName': 'BRIERTON1',
'1.registrationOwners.0customer.militaryExemptionFlag': false,
'1.registrationOwners.0customer.nameSuffix': '',
'1.registrationOwners.0customer.sex': 'M',
'1.registrationOwners.0registrationOwnershipNumber': 1,
'1.shippingAddress.address.addressType': 'S',
'1.shippingAddress.address.city': 'SARASOTA',
'1.shippingAddress.address.countryCode': 'US',
'1.shippingAddress.address.countyCode': 16,
'1.shippingAddress.address.foreignPostalCode': '',
'1.shippingAddress.address.state': 'FL',
'1.shippingAddress.address.streetAddress': '5858 FRUITVILLE RD',
'1.shippingAddress.address.zipCode': 34240,
'1.shippingAddress.address.zipPlus': 5858,
'1.shippingAddress.shippingCompanyName': '',
'1.shippingAddress.shippingName1': 'DAVID1 HUGH1 BRIERTON1',
'1.shippingAddress.shippingName2': '',
'1.stops': '',
'1.vehicle.address': '',
'1.vehicle.bodyCode': '4D',
'1.vehicle.brakeHorsePower': '',
'1.vehicle.cubicCentimeters': '',
'1.vehicle.grossWeight': '',
'1.vehicle.insuranceAffidavitCode': '',
'1.vehicle.leaseOwnerFlag': false,
'1.vehicle.lengthFeet': '',
'1.vehicle.lengthInches': '',
'1.vehicle.majorColorCode': 'BLU',
'1.vehicle.makeCode': 'STRN',
'1.vehicle.minorColorCode': '',
'1.vehicle.netWeight': 2290,
'1.vehicle.numberOfAxles': '',
'1.vehicle.ownerUnitNumber': '',
'1.vehicle.titleNumber': 239874,
'1.vehicle.vehicleIdentificationNumber': '1G832492871Z23094',
'1.vehicle.vehicleNumber': 239084,
'1.vehicle.vehicleType': 'AU',
'1.vehicle.vesselCode': '',
'1.vehicle.vesselManufacturerDesc': '',
'1.vehicle.vesselResidentStatus': 'Y',
'1.vehicle.vesselWaterType': '',
'1.vehicle.widthFeet': '',
'1.vehicle.widthInches': '',
'1.vehicle.yearMake': 2001,
'2.countyCode': 12,
'2.excludedFlag': '',
'2.fees.annualTotalFees': 45.6,
'2.fees.annualTotalFeesMail': 46.35,
'2.fees.annualTotalFeesOnline': 48.35,
'2.fees.biennialTotalFees': 91.2,
'2.fees.biennialTotalFeesMail': 91.95,
'2.fees.biennialTotalFeesOnline': 93.95,
'2.fees.branchFeeFlag': false,
'2.fees.delinquentFeeAmount': 10,
'2.fees.mhBackTax': 0,
'2.fees.mhBackTaxMonths': 0,
'2.fileId': 522,
'2.id': '000e3450d-3454-499a-ae70-de5676577',
'2.messages.0messageCode': 'RN40',
'2.messages.0messageText': 'You must complete the affidavit on the reverse side or provide a copy of your Florida insurance identification card.',
'2.messages.0messageType': 'I',
'2.registrationDetail.annualPlateReplacementFlag': false,
'2.registrationDetail.arfCredit': 8.4,
'2.registrationDetail.biennialPlateReplacementFlag': false,
'2.registrationDetail.customerStopFlag': '',
'2.registrationDetail.delinquentDate': '2018-02-11T00:00:00',
'2.registrationDetail.expirationDate': '2018-01-11T00:00:00',
'2.registrationDetail.foreignAddressFlag': false,
'2.registrationDetail.honorayConsulPlateFlag': '',
'2.registrationDetail.hovDecalNumber': '',
'2.registrationDetail.hovDecalYear': '',
'2.registrationDetail.hovExpirationDate': '',
'2.registrationDetail.inventorySubtype': 'RP',
'2.registrationDetail.lastActivityCounty': 12,
'2.registrationDetail.legislativePlateDueForReplacementFlag': false,
'2.registrationDetail.licensePlateCode': 'RGR',
'2.registrationDetail.licensePlateNumber': '808IUT',
'2.registrationDetail.mailToAddressFlag': false,
'2.registrationDetail.mailToCustomerNumber': '',
'2.registrationDetail.mhLocationCode': '',
'2.registrationDetail.militaryOwnerFlag': false,
'2.registrationDetail.numberOfRegistrants': 1,
'2.registrationDetail.onlineRenewalEligibilityFlag': true,
'2.registrationDetail.pinNumber': 934597,
'2.registrationDetail.plateExpirationDate': '2023-06-06T00:00:00',
'2.registrationDetail.plateIssueDate': '2013-12-06T00:00:00',
'2.registrationDetail.possibleNgExemptionFlag': false,
'2.registrationDetail.registrationNumber': 39287432,
'2.registrationDetail.registrationOnlyFlag': '',
'2.registrationDetail.registrationType': 'R',
'2.registrationDetail.registrationUse': 'PR',
'2.registrationDetail.renewalCountyCode': 12,
'2.registrationDetail.rentalParkFlag': '',
'2.registrationDetail.seminoleMiccosukeeIndianFlag': false,
'2.registrationDetail.taxCollectorRenewalEligibilityFlag': true,
'2.registrationDetail.vehicleClassCode': 1,
'2.registrationOwners.0customer.addresses.0addressType': 'R',
'2.registrationOwners.0customer.addresses.0city': 'SARASOTA',
'2.registrationOwners.0customer.addresses.0countryCode': 'US',
'2.registrationOwners.0customer.addresses.0countyCode': 12,
'2.registrationOwners.0customer.addresses.0foreignPostalCode': '',
'2.registrationOwners.0customer.addresses.0state': 'FL',
'2.registrationOwners.0customer.addresses.0streetAddress': '39875 44TH DR E',
'2.registrationOwners.0customer.addresses.0zipCode': 34243,
'2.registrationOwners.0customer.addresses.0zipPlus': 5566,
'2.registrationOwners.0customer.addresses.1addressType': 'M',
'2.registrationOwners.0customer.addresses.1city': 'PALMETTO',
'2.registrationOwners.0customer.addresses.1countryCode': 'US',
'2.registrationOwners.0customer.addresses.1countyCode': 12,
'2.registrationOwners.0customer.addresses.1foreignPostalCode': '',
'2.registrationOwners.0customer.addresses.1state': 'FL',
'2.registrationOwners.0customer.addresses.1streetAddress': '39875 44TH DR E',
'2.registrationOwners.0customer.addresses.1zipCode': 34221,
'2.registrationOwners.0customer.addresses.1zipPlus': '',
'2.registrationOwners.0customer.companyName': '',
'2.registrationOwners.0customer.customerNumber': 2398574,
'2.registrationOwners.0customer.customerStopFlag': false,
'2.registrationOwners.0customer.customerType': 'I',
'2.registrationOwners.0customer.dateOfBirth': '1958-01-11T00:00:00',
'2.registrationOwners.0customer.dlExpirationDate': '2020-01-11T00:00:00',
'2.registrationOwners.0customer.dlRenewalEligibilityFlag': true,
'2.registrationOwners.0customer.driverLicenseNumber': 'B23987433',
'2.registrationOwners.0customer.emailAddress': '',
'2.registrationOwners.0customer.feidNumber': '',
'2.registrationOwners.0customer.firstName': 'DAVID2',
'2.registrationOwners.0customer.lastName': 'HUGH2',
'2.registrationOwners.0customer.middleName': 'BRIERTON2',
'2.registrationOwners.0customer.militaryExemptionFlag': false,
'2.registrationOwners.0customer.nameSuffix': '',
'2.registrationOwners.0customer.sex': 'M',
'2.registrationOwners.0registrationOwnershipNumber': 1,
'2.shippingAddress.address.addressType': 'S',
'2.shippingAddress.address.city': 'PALMETTO',
'2.shippingAddress.address.countryCode': 'US',
'2.shippingAddress.address.countyCode': 12,
'2.shippingAddress.address.foreignPostalCode': '',
'2.shippingAddress.address.state': 'FL',
'2.shippingAddress.address.streetAddress': '293847 33TH ST W',
'2.shippingAddress.address.zipCode': 34221,
'2.shippingAddress.address.zipPlus': '',
'2.shippingAddress.shippingCompanyName': '',
'2.shippingAddress.shippingName1': 'DAVID2 HUGH2 BRIERTON2',
'2.shippingAddress.shippingName2': '',
'2.stops': '',
'2.vehicle.address': '',
'2.vehicle.bodyCode': '2D',
'2.vehicle.brakeHorsePower': '',
'2.vehicle.cubicCentimeters': '',
'2.vehicle.grossWeight': '',
'2.vehicle.insuranceAffidavitCode': '',
'2.vehicle.leaseOwnerFlag': false,
'2.vehicle.lengthFeet': '',
'2.vehicle.lengthInches': '',
'2.vehicle.majorColorCode': 'BLK',
'2.vehicle.makeCode': 'PONT',
'2.vehicle.minorColorCode': '',
'2.vehicle.netWeight': 3802,
'2.vehicle.numberOfAxles': '',
'2.vehicle.ownerUnitNumber': '',
'2.vehicle.titleNumber': 239857424,
'2.vehicle.vehicleIdentificationNumber': '6G23242312UX63297437',
'2.vehicle.vehicleNumber': '35T7843',
'2.vehicle.vehicleType': 'AU',
'2.vehicle.vesselCode': '',
'2.vehicle.vesselManufacturerDesc': '',
'2.vehicle.vesselResidentStatus': 'Y',
'2.vehicle.vesselWaterType': '',
'2.vehicle.widthFeet': '',
'2.vehicle.widthInches': '',
'2.vehicle.yearMake': 2006 ]
python I got a complex multiple nested JSON file, how to convert to csv file
pd.json_normalize flattens the dictionary to columns. When you have list, you need to explode the list that transform the list into rows.
If you have dictionary inside the list, you need to apply the json_normalize again on the exploded column.
import pandas as pd
data = []
# read_json normalizes the top layer.
df_it = pd.read_json('C:\\Users\\jeri\\Desktop\\1.json', encoding='utf-8', lines=True, chunksize=100000)
for sub in df_it:
data.append(sub)
df = pd.concat(data)
# You just need to flatten the list of dict additionally.
# If you are using pandas >= 1.3.0, df = df.explode(['name', 'info', 'Hobby'])
# Otherwise, explode only takes 1 column at a time.
df = df.explode('name').explode('info').explode("Hobby")
df = pd.concat([df.reset_index(drop=True),
pd.json_normalize(df.name),
# info is reserved keyword (dataframe function name, you cannot use df.info),
# change it to accessing by df['info']
pd.json_normalize(df['info']),
pd.json_normalize(df.Hobby)], axis=1)
# Drop the exploded column, the contents inside the column is extracted to columns already
df = df.drop(['name', 'info', 'Hobby'], axis=1)
df.to_csv('C:\\Users\\jeri\\Desktop\\11.csv', index=False, encoding='utf-8')
Another thing to consider is when you have multiple json_normalize, you need to make sure that key in each dict column is not shared amongst the other columns you try to json_normalize. In your sample, I didn't see any of same key name, so I did concat and json_normalize in 1 shot.
However, if you have same key name in dict, you need to take step by step. Do json_normalize on 1 exploded column and rename column(s), then do another json_normalize on another exploded column.
Flatten a nested JSON with array and filter to CSV
Here is a complete runnable example. It will create a file "C:\test.csv".
There is no "automatic" way of flattening a nested object to a flat object. But you can manually create assign properties to a flat object.
First I parse the JSON text into a powershell object
$obj = @"
{
"createdDateTime": "xxxx-xx-xx",
"receivedDateTime": "xxxx-xx-xx",
"isRead": true,
"from": {
"emailAddress": {
"name": "John",
"adress": "john@onmicrosoftware.com"
}
},
"toRecipients": [
{
"emailAddress": {
"name": "Amy",
"adress": "Amy@onmicrosoftware.com"
}
},
{
"emailAddress": {
"name": "Amy",
"adress": "Amy@onmicrosoftware.com"
}
}
]
}
"@ | ConvertFrom-Json
Now take the Powershell object (or list of objects, this will work even if you have many of these entries) and pipe it to ForEach-Object. Inside the loop map the different properties to a flat object.
$flattened = $obj | ForEach-Object {
return [PSCustomObject]@{
createdDateTime = $_.createdDateTime
receivedDateTime = $_.receivedDateTime
from_name = $_.from.emailAddress.name
from_adress = $_.from.emailAddress.adress
to_name_1 = $_.toRecipients[0].emailAddress.name
to_adress_1 = $_.toRecipients[0].emailAddress.adress
to_name_2 = $_.toRecipients[1].emailAddress.name
to_adress_2 = $_.toRecipients[1].emailAddress.adress
}
}
Now you can export the entire thing as a CSV
$flattened | Export-Csv C:\test.csv -Delimiter ";" -Encoding UTF8
This assumes that there will always be 2 toRecipients. It would be possible to dynamically add to_name_3, to_name_4, and so on if more are encountered, but that's quite a bit more complicated.
Flatten JSON nested key value pairs for conversion to csv
A simple approach there is to change the lists into dicts so that json_normalize can more easily apply its magic:
with a quick re-formatter it could give
def list2dic(inconvenient_list):
reformatted_dic = {}
for item in inconvenient_list:
reformatted_dic[item['label']] = item['value']
return reformatted_dic
Apply it to the contact_email and contact_phone fields:
for contact in data['contacts']:
contact['contact_email'] = list2dict(contact['contact_email'])
contact['contact_phone'] = list2dict(contact['contact_phone'])
and now json_normalize should work just fine (changing the separator from '.' to '_')
info_df = pd.json_normalize(data, 'contacts', sep='_')
How to flatten multilevel/nested JSON?
I used the following function (details can be found here):
def flatten_data(y):
out = {}
def flatten(x, name=''):
if type(x) is dict:
for a in x:
flatten(x[a], name + a + '_')
elif type(x) is list:
i = 0
for a in x:
flatten(a, name + str(i) + '_')
i += 1
else:
out[name[:-1]] = x
flatten(y)
return out
This unfortunately completely flattens whole JSON, meaning that if you have multi-level JSON (many nested dictionaries), it might flatten everything into single line with tons of columns.
What I used, in the end, was json_normalize() and specified structure that I required. A nice example of how to do it that way can be found here.
flattening json to csv with jqplay
Are you looking for something like this?
jq -r '.[].lock_doors[] | [.[]] | @csv'
"OPEN","NR1","NO_DATA","2021-12-29T16:32:23Z",4.28214,51.34426
"CLOSED","NR2","WORKING","2022-01-12T12:32:52Z","2021-12-22T13:13:57Z",4.28247,51.34424
Demo
To add column headers, simply prepend them in an array:
jq -r '["a","b","c"], .[].lock_doors[] | [.[]] | @csv'
"a","b","c"
"OPEN","NR1","NO_DATA","2021-12-29T16:32:23Z",4.28214,51.34426
"CLOSED","NR2","WORKING","2022-01-12T12:32:52Z","2021-12-22T13:13:57Z",4.28247,51.34424
Demo
Convert nested json with nested arrays in csv in python
I managed to figure out the answer using pandas. Here is my alternative:
def read_json(filename: str) -> dict:
try:
with open(filename) as f:
data = json.loads(f.read())
except:
raise Exception(f"Reading {filename} file encountered an error")
return data
def main():
data = read_json(filename='ExtractFile1.json')
df3 = pd.json_normalize(data, record_path=['key_with_document', ['document','struct','MatchResponse']], meta=[['key_with_document', 'document', 'creation_date'],['key_with_document', 'document', 'expiration_date'], ['key_with_document', 'document','modification_date'], ['key_with_document', 'document','revision'], ['key_with_document', 'document','struct','MatchStatus'],['key_with_document', 'document','struct','docType'],['key_with_document', 'document','struct','extRefId1'],['key_with_document', 'document','struct','extRefId1Type'],['key_with_document', 'document','struct','extRefId2'],['key_with_document', 'document','struct','extRefId2Type'],['key_with_document', 'document','struct','Rul'],['key_with_document', 'document','struct','Status'],
['key_with_document','document','struct','dataRefs']])
df3.to_csv('out3.csv')
if __name__ == '__main__':
main()
Convert nested JSON to CSV or table
- standard techniques for dealing with nested json
json_normalize()explode()apply(pd.Series)
- finally some cleanup, drop unwanted rows and replace
nanwith empty string
import json
js = """{"menu": {
"header": "SVG Viewer",
"items": [
{"id": "Open"},
{"id": "OpenNew", "label": "Open New"},
null,
{"id": "ZoomIn", "label": "Zoom In"},
{"id": "ZoomOut", "label": "Zoom Out"},
{"id": "OriginalView", "label": "Original View"},
null,
{"id": "Quality"},
{"id": "Pause"},
{"id": "Mute"},
null,
{"id": "Find", "label": "Find..."},
{"id": "FindAgain", "label": "Find Again"},
{"id": "Copy"},
{"id": "CopyAgain", "label": "Copy Again"},
{"id": "CopySVG", "label": "Copy SVG"},
{"id": "ViewSVG", "label": "View SVG"},
{"id": "ViewSource", "label": "View Source"},
{"id": "SaveAs", "label": "Save As"},
null,
{"id": "Help"},
{"id": "About", "label": "About Adobe CVG Viewer..."}
]
}}"""
df = pd.json_normalize(json.loads(js)).explode("menu.items").reset_index(drop=True)
df.drop(columns=["menu.items"]).join(df["menu.items"].apply(pd.Series)).dropna(subset=["id"]).fillna("")