Hi there,
I want to convert the JSON that the FileMaker Data API returns to a more ‘flat’ structure so that it can easily be exported as an Excel.
So basically, I want to convert this:
[
[
{
"messages": [
{
"code": "0",
"message": "OK"
}
],
"convert": [
{
"convert": "Address: item.fieldData.address,City: item.fieldData.city"
}
],
"response": {
"data": [
{
"fieldData": {
"address": "669 Packerland Dr #1438",
"city": "Denver",
"company_name": "Super 8 Motel",
"county": "Denver",
"email": "[email protected]",
"first_name": "Pamella",
"last_name": "Fortino",
"phone1": "303-404-2210",
"phone2": "303-794-1341",
"state": "CO",
"web": "http://www.supermotel.com",
"zip": "80212"
},
"modId": "0",
"portalData": {
},
"recordId": "265"
},
{
"fieldData": {
"address": "7563 Cornwall Rd #4462",
"city": "Denver",
"company_name": "American Speedy Printing Ctrs",
"county": "Lancaster",
"email": "[email protected]",
"first_name": "Felix",
"last_name": "Hirpara",
"phone1": "717-491-5643",
"phone2": "717-583-1497",
"state": "PA",
"web": "http://www.americanspeedyprintingctrs.com",
"zip": "17517"
},
"modId": "0",
"portalData": {
},
"recordId": "299"
},
{
"fieldData": {
"address": "76598 Rd I 95 #1",
"city": "Denver",
"company_name": "Cullen, Terrence P Esq",
"county": "Denver",
"email": "[email protected]",
"first_name": "Virgie",
"last_name": "Kiel",
"phone1": "303-776-7548",
"phone2": "303-845-5408",
"state": "CO",
"web": "http://www.cullenterrencepesq.com",
"zip": "80216"
},
"modId": "0",
"portalData": {
},
"recordId": "422"
},
{
"fieldData": {
"address": "866 34th Ave",
"city": "Denver",
"company_name": "Asendorf, J Alan Esq",
"county": "Denver",
"email": "[email protected]",
"first_name": "Howard",
"last_name": "Paulas",
"phone1": "303-623-4241",
"phone2": "303-692-3118",
"state": "CO",
"web": "http://www.asendorfjalanesq.com",
"zip": "80231"
},
"modId": "0",
"portalData": {
},
"recordId": "132"
}
],
"dataInfo": {
"database": "JSONviaExecuteSQL",
"foundCount": 4,
"layout": "Contacts",
"returnedCount": 4,
"table": "Contacts",
"totalRecordCount": 500
}
}
}
]
]
into:
[
{
"address": "669 Packerland Dr #1438",
"city": "Denver",
"company_name": "Super 8 Motel",
"county": "Denver",
"email": "[email protected]",
"first_name": "Pamella",
"last_name": "Fortino",
"phone1": "303-404-2210",
"phone2": "303-794-1341",
"state": "CO",
"web": "http://www.supermotel.com",
"zip": "80212"
},
{
"address": "7563 Cornwall Rd #4462",
"city": "Denver",
"company_name": "American Speedy Printing Ctrs",
"county": "Lancaster",
"email": "[email protected]",
"first_name": "Felix",
"last_name": "Hirpara",
"phone1": "717-491-5643",
"phone2": "717-583-1497",
"state": "PA",
"web": "http://www.americanspeedyprintingctrs.com",
"zip": "17517"
},
{
"address": "76598 Rd I 95 #1",
"city": "Denver",
"company_name": "Cullen, Terrence P Esq",
"county": "Denver",
"email": "[email protected]",
"first_name": "Virgie",
"last_name": "Kiel",
"phone1": "303-776-7548",
"phone2": "303-845-5408",
"state": "CO",
"web": "http://www.cullenterrencepesq.com",
"zip": "80216"
},
{
"address": "866 34th Ave",
"city": "Denver",
"company_name": "Asendorf, J Alan Esq",
"county": "Denver",
"email": "[email protected]",
"first_name": "Howard",
"last_name": "Paulas",
"phone1": "303-623-4241",
"phone2": "303-692-3118",
"state": "CO",
"web": "http://www.asendorfjalanesq.com",
"zip": "80231"
}
]
I figured out that the Rename Keys Node unfortunately does not work with arrays.
I then found a function in this forum which I modified to:
const tmp = [];
for (item of items[0].json[0]["response"]["data"]) {
tmp.push({json:{Address: item.fieldData.address,City: item.fieldData.city ... ETC ...}})
}
return tmp;
Now I would lik to convert this code into more generic code, so that I will automatically take all the data keys instead of having to hard code them into this function.
Can anybody please explain me how to do this?
Thanks in advance and best regards - Dick