Question 109592 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – JQ – Groupby and concatenate text objects

Not quite getting it. I can produce multiple lines but cannot get multiple entries to combine. Looking to take Source JSON and output to CSV as shown:

Source JSON:

[{"State": "NewYork","Drivers": [
{"Car": "Jetta","Users": [{"Name": "Steve","Details": {"Location": "Home","Time": "9a-7p"}}]},
{"Car": "Jetta","Users": [{"Name": "Roger","Details": {"Location": "Office","Time": "3p-6p"}}]},
{"Car": "Ford","Users": [{"Name": "John","Details": {"Location": "Home","Time": "12p-5p"}}]}
]}]

Desired CSV:

"NewYork","Jetta","Steve;Roger","Home;Office","9a-7p;3p-6p"  
"NewYork","Ford","John","Home","12p-5p"

JQ code that does not work:

.[] | .Drivers[] | .Car as $car |
    .Users[] |  
    [$car, .Name] | @csv
Tags:

3

Answers


  1. 0

    Not quite optimised, but I though’t I’d share the general idea:

    jq -r 'map(.State as $s | 
    (.Drivers | group_by(.Car))[] 
    | [ 
        $s, 
        (map(.Users[].Name) | join(";")),
        (map(.Users[].Details.Location) | join(";")),
        (map(.Users[].Details.Time) | join(";"))
    ])
[] | @csv' b
    • map() over each state, remember the name (map(.State as $s | )
    • group_by(.Car)
    • Create an array containing all your fields that is passed to @csv
      • Use map() and join() to create the fields for Name, Location and Time
        This part could be improved so you don’t need that duplicated part

    Output (with --raw-output:

    "NewYork","John","Home","12p-5p"
"NewYork","Steve;Roger","Home;Office","9a-7p;3p-6p"

    JqPlay seems down, so I’m still searching for an other way of sharing a public demo

    Login or Signup to reply.
  2. 0

    You’re looking for something like this:

    .[] | [.State] + (
  .Drivers | group_by(.Car)[] | [.[0].Car] + (
    map(.Users) | add | [
      map(.Name),
      map(.Details.Location),
      map(.Details.Time)
    ] | map(join(";"))
  )
) | @csv

    $ jq -r -f tst.jq file
"NewYork","Ford","John","Home","12p-5p"
"NewYork","Jetta","Steve;Roger","Home;Office","9a-7p;3p-6p"
$
    Login or Signup to reply.
  3. 0

    Far from perfect, but it builds the result incrementally so it should be easily debuggable and extensible:

    map({State} + (.Drivers[] | {Car} + (.Users[] | {Name} + (.Details | {Location, Time}))))
| group_by(.Car)
| map(reduce .[] as $item (
  {State:null,Car:null,Name:[],Location:[],Time:[]};
  . + ($item | {State,Car}) | .Name += [$item.Name] | .Location += [$item.Location] | .Time += [$item.Time]))
| .[]
| [.State, .Car, (.Name,.Location,.Time|join(","))]
| @csv
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search