Deeply nested JSON data issues

[paulswanson22]

Hello,

I'm trying to parse out a JSON response from a connector for my school, but I'm getting some strange responses. The data that comes back is for academic years and terms, and looks like:

"body": {
"academic_years": {
"diploma": {
"academic_years": [
{
"id": 5309,
"name": "January – June 2011",
"starts_on": "2011-01-22",
"ends_on": "2011-06-03",
"updated_at": "2015-09-22T03:38:35.000-04:00",
"academic_terms": [
{
"id": 1592,
"name": "Quarter 3",
"starts_on": "2011-01-22",
"ends_on": "2011-04-01",
"updated_at": "2015-09-22T03:38:35.000-04:00",
"exam_grade": false
},
{
"id": 1593,
"name": "Quarter 4",
"starts_on": "2011-04-20",
"ends_on": "2011-06-03",
"updated_at": "2015-09-22T03:38:35.000-04:00",
"exam_grade": false
}
]
},
etc

The problem is that the term "academic_years" appears twice in the JSON and it seems to be giving Airbyte some difficulties. When I use a Field Path of "academic_years", "diploma" it also strips out the second "academic_years", and gives a response of:

[
{
"id": 5309,
"name": "January – June 2011",
"starts_on": "2011-01-22",
"ends_on": "2011-06-03",
"updated_at": "2015-09-22T03:38:35.000-04:00",
"academic_terms": [
{
"id": 1592,
"name": "Quarter 3",
"starts_on": "2011-01-22",
"ends_on": "2011-04-01",
"updated_at": "2015-09-22T03:38:35.000-04:00",
"exam_grade": false
},
{
"id": 1593,
"name": "Quarter 4",
"starts_on": "2011-04-20",
"ends_on": "2011-06-03",
"updated_at": "2015-09-22T03:38:35.000-04:00",
"exam_grade": false
}
]
},

When I try to further specify within this, I get no records. If I try a field path of "academic_years", "diploma", "academic_years" I get a Python error.

Has anyone encountered this issue before?

[m-sefidrou]

Hello

This happens because the key name academic_years appears twice in your JSON — one at the top level and one inside "diploma".
Airbyte gets confused when two keys have the same name in different levels.

You can fix it by using the full path:

body → academic_years → diploma → academic_years

If that still doesn’t work, try cleaning the JSON first.
Here’s a simple Python example that extracts the correct part:

import json
with open("input.json") as f:
    data = json.load(f)
records = data["body"]["academic_years"]["diploma"]["academic_years"]
with open("cleaned.json", "w") as f:
    json.dump(records, f, indent=2)

Then use cleaned.json in Airbyte — it should work fine. Good Luck!