JQ to CSV issues

user_13058320 0
Viewed 111

I previously got some help on here for some jq to csv issues. I ran into an issue where a few json files had some extra values that breaks the jq command

Here is the json data. The repairs section is what breaks the jq command

[
    {
        "Name": "John Doe",
        "Car": [
            "Car1",
            "Car2"
        ],
        "Location": "Texas",
        "Repairs: {
            "RepairLocations": {
                "RepairsCompleted":[
                    "Fix1",
                    "Fix2"
                ]
            }
        }
    },
    {
        "Name": "Jane Roe",
        "Car": "Car1",            
        "Location": [
            "Illinois",
            "Kansas"
        ]
    }
]

Here is the command 

def expand($keys):
    . as $in
    | reduce $keys[] as $k ( [{}];
        map(. + { 
            ($k): ($in[$k] | if type == "array" then .[] else . end)
        })
    ) | .[];
(.[0] | keys_unsorted) as $h
| $h, (.[] | expand($h) | [.[$h[]]]) | @csv

This is the end result i am trying to get. This data isnt actual data.

Name,Car,Location,Repairs:RepairLocation
John Doe,Car1,Texas,RepairsCompleted:Fix1
John Doe,Car1,Texas,RepairsCompleted:Fix2
John Doe,Car2,Texas,RepairsCompleted:Fix1
John Doe,Car2,Texas,RepairsCompleted:Fix2
Jane Roe,Car1,Illinois,
Jane Roe,Car1,Kansas,

Any advice on this would be great. I am struggling to figure jq out

jq export-to-csv json
1 Answers

A simple solution can be obtained using the same technique shown in one of the answers to the similar question you already asked. The only difference is fulfilling your requirements in the case where the "Repairs" key does not exist:

["Name", "Car", "Location", "Repairs:RepairLocation"],
(.[]
 | [.Name]
    + (.Car|..|scalars|[.])
    + (.Location|..|scalars|[.])
    + (.Repairs|..|scalars
       | [if . == null then . else "RepairsCompleted:\(.)" end]) )
| @csv

Avoiding the repetition with a helper function

def s: .. | scalars | [.];

["Name", "Car", "Location", "Repairs:RepairLocation"],
(.[]
 | [.Name] 
    + (.Car|s)
    + (.Location|s) 
    + (.Repairs|s|map(if . == null then . else "RepairsCompleted:\(.)" end)))
| @csv
user_9973580
Related