Nested json - store values in csv

user_14547625 0
Viewed 30

I am trying to convert a nested json file into csv. It's data from a darts API and the structure is always the same. Nevertheless I got some problems flattening and storing the values in a csv because of the nested structure.

json:

{
"summaries": [{
    "sport_event": {
        "id": "sr:sport_event:12967512",
        "start_time": "2017-11-11T13:15:00+00:00",
        "start_time_confirmed": true,
        "sport_event_context": {
            "sport": {
                "id": "sr:sport:22",
                "name": "Darts"
            },
            "category": {
                "id": "sr:category:104",
                "name": "International"
            },
            "competition": {
                "id": "sr:competition:597",
                "name": "Grand Slam of Darts"
            },
            "season": {
                "id": "sr:season:47332",
                "name": "Grand Slam of Darts 2017",
                "start_date": "2017-11-11",
                "end_date": "2017-11-20",
                "year": "2017",
                "competition_id": "sr:competition:597"
            },
            "stage": {
                "order": 1,
                "type": "league",
                "phase": "stage_1",
                "start_date": "2017-11-11",
                "end_date": "2017-11-15",
                "year": "2017"
            },
            "round": {
                "number": 1
            },
            "groups": [{
                "id": "sr:league:29766",
                "name": "Grand Slam of Darts 2017, Group G",
                "group_name": "G"
            }]
        },
        "coverage": {
            "live": true
        },
        "competitors": [{
            "id": "sr:competitor:35936",
            "name": "Smith, Michael",
            "abbreviation": "SMI",
            "qualifier": "home"
        }, {
            "id": "sr:competitor:83895",
            "name": "Wilson, James",
            "abbreviation": "WIL",
            "qualifier": "away"
        }]
    },
    "sport_event_status": {
        "status": "closed",
        "match_status": "ended",
        "home_score": 5,
        "away_score": 3,
        "winner_id": "sr:competitor:35936"
    }
}, {
    "sport_event": {
        "id": "sr:sport_event:12967508",
        "start_time": "2017-11-11T13:40:00+00:00",
        "start_time_confirmed": true,
        "sport_event_context": {
            "sport": {
                "id": "sr:sport:22",
                "name": "Darts"
            },
            "category": {
                "id": "sr:category:104",
                "name": "International"
            },
            "competition": {
                "id": "sr:competition:597",
                "name": "Grand Slam of Darts"
            },
            "season": {
                "id": "sr:season:47332",
                "name": "Grand Slam of Darts 2017",
                "start_date": "2017-11-11",
                "end_date": "2017-11-20",
                "year": "2017",
                "competition_id": "sr:competition:597"
            },
            "stage": {
                "order": 1,
                "type": "league",
                "phase": "stage_1",
                "start_date": "2017-11-11",
                "end_date": "2017-11-15",
                "year": "2017"
            },
            "round": {
                "number": 1
            },
            "groups": [{
                "id": "sr:league:29764",
                "name": "Grand Slam of Darts 2017, Group F",
                "group_name": "F"
            }]
        },
        "coverage": {
            "live": true
        },
        "competitors": [{
            "id": "sr:competitor:70916",
            "name": "Bunting, Stephen",
            "abbreviation": "BUN",
            "qualifier": "home"
        }, {
            "id": "sr:competitor:191262",
            "name": "de Zwaan, Jeffrey",
            "abbreviation": "DEZ",
            "qualifier": "away"
        }]
    },
    "sport_event_status": {
        "status": "closed",
        "match_status": "ended",
        "home_score": 5,
        "away_score": 4,
        "winner_id": "sr:competitor:70916"
    }
}

So for each sport_event I would like to store the variables:

  • "start_time"
  • from "season" the variable "name"
  • from "competitors" both "id" and "name"
  • from "sport_event_status" the "winner_id"

I have already tried to flatten the json file with this code:

import json

f = open(r'path of file.json')
data = json.load(f)

def flatten(data):
    for key,value in data.items():
        print (str(key)+'->'+str(value))
        if type(value) == type(dict()):
            flatten(value)
        elif type(value) == type(list()):
            for val in value:
                if type(val) == type(str()):
                    pass
                elif type(val) == type(list()):
                    pass
                else:
                    flatten(val)
flatten(data)
print(data)

This actually prints out the following:

id->sr:season:47332
name->Grand Slam of Darts 2017
start_date->2017-11-11
end_date->2017-11-20
year->2017
competition_id->sr:competition:597

Now my question is how to store the values I mentioned above in a csv file.

Thanks in advance for your support.

export-to-csv csv nested json
1 Answers

Using jq, you basically just have to transcribe your specification, adding a bit of context and taking care of an embedded array:

.summaries[]
| .sport_event                       # Your specification:
| [.start_time,                      # start_time
   .sport_event_context.season.name] # from "season" the variable "name"
  + [.competitors[] | .id, .name]    # from "competitors" both "id" and "name"
  + [.sport_event_status.winner_id]  # from "sport_event_status" the "winner_id"
| @csv

Invocation

E.g.

jq -rf program.jq my.json
user_9973580
Related