Sammy
Reputation: 21
Extract data from Json using Unix
I want to extract data from the below JSON file:
{
"fellowId": 101777,
"srId": "wfakfjaslkf7kev",
"goId": "x21akjsdfvg",
"people": {
"Date": "2020-10-30",
"computerID": {
"id": "772",
"nameid": {
"name": "student",
"code": "std"
}
},
"Data": {
"personName": {
"givenName": "john",
"familyName": "doe"
},
"communication": {
"telephones": [{}],
"emails": [{
"emailid": "[email protected]"
}],
"ia": []
},
"address": {
"lineOne": "124 Marie Drive",
"cityName": "Lake",
"sd1": {
"name": "MA"
},
"country": "LA",
"postalCode": "12345"
}
},
"educationHistory": [{
"edu": "st mary high school",
"EntityID": {
"EntityID": "3371",
"country": null
},
"sAddress": {
"lineOne": "PO Box 4463",
"cityName": "bridge",
"sd1": {
"name": "MA"
},
"country": "LA",
"postalCode": "74525"
},
"reopenDate": "2021-09-01",
"workerStatusCode": {
"name": "Active",
"code": "A"
},
"durationCourse": {
"name": "Full time",
"code": "Full time"
},
"schoolStartDate": "2020-01-07",
"Duration": "O0U4N2D",
"Hours": 166,
"feeSummary": [{
"startyear": 2020,
"fee1": {
"f2fAmount": 9168.0,
"currency": "rs"
},
"activitybelongingf2fAmount": [{
"type": "tution",
"rf2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
}
}, {
"rTypeCode": "YTD sent",
"rf2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
}
}, {
"rTypeCode": "YTD Others",
"rf2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
}
}],
"totalf2fAmount": {
"f2fAmount": 9168.0,
"ccode": "ben"
},
"belongingablef2fAmount": {
"f2fAmount": 7487.2,
"ccode": "ben"
}
}],
"fee7History": [{
"acadamyReedem": true,
"qDIndicator": null,
"belongingDate": "2020-10-30",
"belongingPeriod": {
"startDate": "2020-10-01",
"endDate": "2020-10-31"
},
"belongingf2fAmount": {
"g2gf2f": {
"f2fAmount": 1247.0,
"ccode": "ben"
},
"prioritybelongingf2fAmount": {
"f2fAmount": 1247.0,
"ccode": "ben"
},
"netPf2f": {
"f2fAmount": 3743.6,
"ccode": "ben"
},
"bbelongingf2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
},
"activitybelongingf2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
},
"otherbelongingf2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
}
},
"durationHours": "166.0",
"BaCode": {
"name": "marks",
"code": null
},
"bPRate": {
"f2fAmount": 1247.0,
"ccode": "ben"
},
"CycleCode": {
"name": "yearly",
"code": "M"
},
"belongingDistributions": [{
"depositf2fAmount": {
"f2fAmount": 3743.6,
"ccode": "ben"
},
"deAccount": {
"transportid": "260",
"studNumber": "1949",
"accountTypeCode": "geography"
}
}],
"donations": [{
"dCode": {
"name": "secondary donations",
"code": null
},
"donationoff2fAmount": {
"f2fAmount": 852.16,
"ccode": "ben"
}
}, {
"dCode": {
"name": "house donations",
"code": null
},
"donationoff2fAmount": {
"f2fAmount": 220.56,
"ccode": "ben"
}
}, {
"dCode": {
"name": "cloths donations",
"code": null
},
"donationoff2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
}
}, {
"dCode": {
"name": "Social donations",
"code": null
},
"donationoff2fAmount": {
"f2fAmount": 284.21,
"ccode": "ben"
}
}, {
"dCode": {
"name": "medical donations",
"code": null
},
"donationoff2fAmount": {
"f2fAmount": 66.47,
"ccode": "ben"
}
}, {
"dCode": {
"name": "uuui donations",
"code": null
},
"donationoff2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
}
}, {
"dCode": {
"name": "food donations",
"code": null
},
"donationoff2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
}
}]
}, {
"acadamyReedem": false,
"qDIndicator": null,
"belongingDate": "2020-10-01",
"belongingPeriod": {
"startDate": "2020-09-01",
"endDate": "2020-09-30"
},
"belongingf2fAmount": {
"g2gf2f": {
"f2fAmount": 1247.0,
"ccode": "ben"
},
"prioritybelongingf2fAmount": {
"f2fAmount": 1247.0,
"ccode": "ben"
},
"netPf2f": {
"f2fAmount": 3743.6,
"ccode": "ben"
},
"bbelongingf2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
},
"activitybelongingf2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
},
"otherbelongingf2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
}
},
"durationHours": "0.0",
"BaCode": {
"name": "marks",
"code": null
},
"bPRate": {
"f2fAmount": 1247.0,
"ccode": "ben"
},
"CycleCode": {
"name": "yearly",
"code": "M"
},
"belongingDistributions": [{
"depositf2fAmount": {
"f2fAmount": 3743.6,
"ccode": "ben"
},
"deAccount": {
"transportid": "260",
"studNumber": "8149",
"accountTypeCode": "geography"
}
}],
"donations": [{
"dCode": {
"name": "secondary donations",
"code": null
},
"donationoff2fAmount": {
"f2fAmount": 852.16,
"ccode": "ben"
}
}, {
"dCode": {
"name": "house donations",
"code": null
},
"donationoff2fAmount": {
"f2fAmount": 220.56,
"ccode": "ben"
}
}, {
"dCode": {
"name": "cloths donations",
"code": null
},
"donationoff2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
}
}, {
"dCode": {
"name": "Social donations",
"code": null
},
"donationoff2fAmount": {
"f2fAmount": 284.21,
"ccode": "ben"
}
}, {
"dCode": {
"name": "medical donations",
"code": null
},
"donationoff2fAmount": {
"f2fAmount": 66.47,
"ccode": "ben"
}
}, {
"dCode": {
"name": "uuui donations",
"code": null
},
"donationoff2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
}
}, {
"dCode": {
"name": "food donations",
"code": null
},
"donationoff2fAmount": {
"f2fAmount": 0.0,
"ccode": "ben"
}
}]
}]
}],
"historySummary": {
"availableStay": 1,
"includedSourceCount": 1,
"availableHistoryMonths": {
"DataMonthCount": 1,
"qDataMCount": 0
}
},
"ffsaId": "21567-4565125-451284512-852741963",
"stayProvider": "usssslad"
}
}
Expected Output:
In case the image doesn't open this is how I want the data once delimited in excel sheet (pipe in below output represent the lines in excel sheet ). I have tried with jq query and was able to extract the data but all my objects got extracted in 1st and 2nd column and not in its respective columns. Need the solution in UNIX only. Also, I need to extract data for all columns and not only the mentioned below. The file is going to be dynamic with n number of rows so the solution is expected to be working for all JSON files (of course the headings remain/ columns remain same, data in rows is only dynamic) :
fellowId | srId |belongingf2fAmount | f2fAmount
101777 | wfakfjaslkf7kev | g2gf2f |1247.0
| | prioritybelongingf2fAmount |1247.0
| | netPf2f |3743.6
| |bbelongingf2fAmount |0.0
| |activitybelongingf2fAmount |0.0
| |otherbelongingf2fAmount |0.0
Upvotes: 0
Views: 87
Answers (1)
Raman Sailopal
Reputation: 12887
Use jq and pipe the output to printf to format the data as required:
jq '.fellowId,.srId,"g2gf2f",.people.educationHistory[].fee7History[0].belongingf2fAmount.g2gf2f.f2fAmount,"prioritybelongingf2fAmount",.people.educationHistory[].fee7History[0].belongingf2fAmount.g2gf2f.f2fAmount,"netPf2f",.people.educationHistory[].fee7History[0].belongingf2fAmount.netPf2f.f2fAmount,"bbelongingf2fAmount",.people.educationHistory[].fee7History[0].belongingf2fAmount.bbelongingf2fAmount.f2fAmount,"activitybelongingf2fAmount",.people.educationHistory[].fee7History[0].belongingf2fAmount.activitybelongingf2fAmount.f2fAmount,"otherbelongingf2fAmount",.people.educationHistory[].fee7History[0].belongingf2fAmount.otherbelongingf2fAmount.f2fAmount' file | xargs printf "%s\t| %s\t| %s\t\t\t| %.1f\t\n\t|\t\t\t| %s\t| %.1f\t\n\t|\t\t\t| %s\t\t\t| %.1f\t\n\t|\t\t\t| %s\t\t| %.1f\t\n\t|\t\t\t| %s\t| %.1f\t\n\t|\t\t\t| %s\t| %.1f\t\n"
Output:
101777 | wfakfjaslkf7kev | g2gf2f | 1247.0
| | prioritybelongingf2fAmount | 1247.0
| | netPf2f | 3743.6
| | bbelongingf2fAmount | 0.0
| | activitybelongingf2fAmount | 0.0
| | otherbelongingf2fAmount | 0.0
Upvotes: 1
Related Questions
- Parsing JSON with Unix tools
- Parse JSON in shell
- Extract values from JSON object using standard utilities
- parsing/extract json data with jq
- How to use the linux command line json parser (jq) to extract a specific value?
- need to extract specific string with JQ
- jq extract data of json
- How to extract fields from a json string using jq
- extract value from json file
- Using jq to extract certain info from JSON file