Bad JSON to Custom JSON

Question

I am currently trying to massage undesirable JSON output that I am returning into a format that is more valuable to me. Answers should be using jq or Python code (I am using the pyjq library in python)

Here is the current JSON output:

[
 {"colour":"Red", "car": [ {"year":2000, "make":"Honda", "model":"Accord"} ] },
 {"colour":"Blue", "car": [ {"year":2015, "make":"Toyota", "model":"Camry"} ] },
 {"colour":"Red", "car": [ {"year":1999, "make":"Dodge", "model":"Ram"} ] }
]

Using jq or possibly looping through using Python, I would like to format this into a new JSON object that looks like this:

[
 { "Red":[2000,1999] },
 { "Blue": 2015 }
]

Does anybody know how I can format any JSON that looks like the first snippet above, and turn it into the desired outcome listed in the second snippet.

Answer 1

If you loop through the data you can reform a new dict like:

Code:

output = {}
for datum in data:
    for car in datum['car']:
        output.setdefault(datum['colour'], []).append(car['year'])

Test Code:

data = [
    {"colour": "Red",
     "car": [{"year": 2000, "make": "Honda", "model": "Accord"}]},
    {"colour": "Blue",
     "car": [{"year": 2015, "make": "Toyota", "model": "Camry"}]},
    {"colour": "Red",
     "car": [{"year": 1999, "make": "Dodge", "model": "Ram"}]}
]

output = {}
for datum in data:
    for car in datum['car']:
        output.setdefault(datum['colour'], []).append(car['year'])
print(output)

Results:

{'Red': [2000, 1999], 'Blue': [2015]}

Answer 2

jq is perfect for this. Let's see what it can do. First, let's group the cars by color. group_by() takes an array and groups the elements by whatever sub-expression you want.

$ jq 'group_by(.colour)' input.json
[
    [
        {"car":[{"model":"Camry","make":"Toyota","year":2015}],"colour":"Blue"}
    ],
    [
        {"car":[{"model":"Accord","make":"Honda","year":2000}],"colour":"Red"},
        {"car":[{"model":"Ram",   "make":"Dodge","year":1999}],"colour":"Red"}
    ]
]

Then let's map each sub-array to a {color: [years]} object. The map() function takes an array and changes each input element into some new output element. We can use it to create the exact {color: [years]} objects we desire.

Let's start with a partial mapping. We'll pull out the colors and see what that looks like.

$ jq -c 'group_by(.colour) | map({(.[0].colour): []})' input.json
[{"Blue":[]},{"Red":[]}]

Perfect. We have the colors. Now to get the list of years we use map again, turn the sub-arrays of cars into arrays of years.

$ jq -c 'group_by(.colour) | map({(.[0].colour): map(.car[].year)})' input.json
[{"Blue":[2015]},{"Red":[2000,1999]}]