Create a .csv File from JSON Data Using JQ

Question

I thought that I knew enough about jq to be able to format .csv files the way I want, but there is always a trick that I have missed!.

My API download looks like this....

{
  "status": "ok",
  "meta": {
    "count": 4
  },
  "data": {
    "1019761328": {
      "achievements": {
        "medalCarius": 1,
        "medalHalonen": 3,
        "aimer": 6,
        "invader": 13,
        "armorPiercer": 18,
        "medalMonolith": 2,
        "medalEkins": 1,
        "medalKay": 2,
        "duelist": 409,
        "newMeritPM2": 1,
        "readyForBattleLT": 4,
        "defender": 15,
        "readyForBattleATSPG": 4,
        "medalLeClerc": 2,
        "demolition": 112,
        "supporter": 13,
        "steelwall": 107,
        "medalLehvaslaiho": 28,
        "medalAbrams": 2,
        "readyForBattleSPG": 4,
        "medalPoppel": 1,
        "medalPascucci": 68,
        "reliableComrade": 303,
        "NY19A1": 1,
        "NY19A2": 1,
        "tankwomen": 1,
        "luckyDevil": 10,
        "NY18A3": 1,
        "NY18A2": 1,
        "mainGun": 28,
        "NY18A1": 1,
        "sinai": 5,
        "firstMerit": 1,
        "medalOrlik": 8,
        "bonecrusher": 824,
        "titleSniper": 41,
        "warrior": 5,
        "ironMan": 130,
        "huntsman": 2,
        "even": 35,
        "medalKolobanov": 1,
        "scout": 4,
        "beasthunter": 5,
        "kamikaze": 30,
        "02YearsOfService": 1,
        "tankExpert2": 1,
        "tankExpert1": 1,
        "readyForBattleMT": 4,
        "tankExpert7": 1,
        "tankExpert6": 1,
        "sniper2": 10,
        "arsonist": 106,
        "charmed": 194,
        "medalBillotte": 1,
        "fighter": 147,
        "medalLavrinenko": 2,
        "impenetrable": 155,
        "sturdy": 65,
        "NY19A3": 1,
        "medalKursk": 1,
        "soldierOfFortune": 4,
        "handOfDeath": 4,
        "DdaymarathonMedal": 1,
        "shootToKill": 3029,
        "medalDumitru": 3,
        "evileye": 8,
        "medalKnispel": 1
      },
      "frags": {
        "crucialShotMedal": 0,
        "prematureDetonationMedal": 0,
        "sentinelMedal": 0,
        "infiltratorMedal": 0,
        "fightingReconnaissanceMedal": 0,
        "fireAndSteelMedal": 0,
        "rangerMedal": 0,
        "reliableComrade": 29,
        "pyromaniacMedal": 0,
        "wolfAmongSheepMedal": 0,
        "heavyFireMedal": 0,
        "bruteForceMedal": 0,
        "guerrillaMedal": 0,
        "promisingFighterMedal": 0,
        "beasthunter": 595,
        "geniusForWarMedal": 0,
        "sinai": 523,
        "pattonValley": 62
      },
      "max_series": {
        "armorPiercer": 18,
        "aimer": 6,
        "titleSniper": 41,
        "deathTrack": 0,
        "invincible": 3,
        "victoryMarch": 0,
        "EFC2016": 0,
        "diehard": 6,
        "WFC2014": 0,
        "tacticalBreakthrough": 0,
        "handOfDeath": 4
      }
    },
    "1034967155": {
      "achievements": {},
      "frags": {
        "crucialShotMedal": 0,
        "prematureDetonationMedal": 0,
        "sentinelMedal": 0,
        "infiltratorMedal": 0,
        "fightingReconnaissanceMedal": 0,
        "fireAndSteelMedal": 0,
        "rangerMedal": 0,
        "reliableComrade": 0,
        "pyromaniacMedal": 0,
        "wolfAmongSheepMedal": 0,
        "heavyFireMedal": 0,
        "bruteForceMedal": 0,
        "guerrillaMedal": 0,
        "promisingFighterMedal": 0,
        "beasthunter": 0,
        "geniusForWarMedal": 0,
        "sinai": 0,
        "pattonValley": 0
      },
      "max_series": {
        "armorPiercer": 0,
        "aimer": 0,
        "titleSniper": 0,
        "deathTrack": 0,
        "invincible": 0,
        "victoryMarch": 0,
        "EFC2016": 0,
        "diehard": 0,
        "WFC2014": 0,
        "tacticalBreakthrough": 0,
        "handOfDeath": 0
      }
    }
  }
}

My .csv output must contain an ID field, a Medal field and a field for # of Medals and look like this....

1019761328,"medalCarius",1
1019761328,"medalHalonen",3
1019761328,"aimer",6

...... etc. repeated for every ID

So far, these commands strip out the data I need....

jq -r '.data | to_entries[] | {id: .key, val: .value[]} '

resulting in....

{
  "id": "1019761328",
  "val": {
    "medalCarius": 1,
    "medalHalonen": 3,
    "aimer": 6,
    "invader": 13,
    "armorPiercer": 18,
    "medalMonolith": 2,
    "medalEkins": 1,
    "medalKay": 2,
    "duelist": 409,
    "newMeritPM2": 1,
    "readyForBattleLT": 4,
    "defender": 15,
    "readyForBattleATSPG": 4,
    "medalLeClerc": 2,
    "demolition": 112,
    "supporter": 13,
    "steelwall": 107,
    "medalLehvaslaiho": 28,
    "medalAbrams": 2,
    "readyForBattleSPG": 4,
    "medalPoppel": 1,
    "medalPascucci": 68,
    "reliableComrade": 303,
    "NY19A1": 1,
    "NY19A2": 1,
    "tankwomen": 1,
    "luckyDevil": 10,
    "NY18A3": 1,
    "NY18A2": 1,
    "mainGun": 28,
    "NY18A1": 1,
    "sinai": 5,
    "firstMerit": 1,
    "medalOrlik": 8,
    "bonecrusher": 824,
    "titleSniper": 41,
    "warrior": 5,
    "ironMan": 130,
    "huntsman": 2,
    "even": 35,
    "medalKolobanov": 1,
    "scout": 4,
    "beasthunter": 5,
    "kamikaze": 30,
    "02YearsOfService": 1,
    "tankExpert2": 1,
    "tankExpert1": 1,
    "readyForBattleMT": 4,
    "tankExpert7": 1,
    "tankExpert6": 1,
    "sniper2": 10,
    "arsonist": 106,
    "charmed": 194,
    "medalBillotte": 1,
    "fighter": 147,
    "medalLavrinenko": 2,
    "impenetrable": 155,
    "sturdy": 65,
    "NY19A3": 1,
    "medalKursk": 1,
    "soldierOfFortune": 4,
    "handOfDeath": 4,
    "DdaymarathonMedal": 1,
    "shootToKill": 3029,
    "medalDumitru": 3,
    "evileye": 8,
    "medalKnispel": 1
  }
}
{
  "id": "1019761328",
  "val": {
    "crucialShotMedal": 0,
    "prematureDetonationMedal": 0,
    "sentinelMedal": 0,
    "infiltratorMedal": 0,
    "fightingReconnaissanceMedal": 0,
    "fireAndSteelMedal": 0,
    "rangerMedal": 0,
    "reliableComrade": 29,
    "pyromaniacMedal": 0,
    "wolfAmongSheepMedal": 0,
    "heavyFireMedal": 0,
    "bruteForceMedal": 0,
    "guerrillaMedal": 0,
    "promisingFighterMedal": 0,
    "beasthunter": 595,
    "geniusForWarMedal": 0,
    "sinai": 523,
    "pattonValley": 62
  }
}
{
  "id": "1019761328",
  "val": {
    "armorPiercer": 18,
    "aimer": 6,
    "titleSniper": 41,
    "deathTrack": 0,
    "invincible": 3,
    "victoryMarch": 0,
    "EFC2016": 0,
    "diehard": 6,
    "WFC2014": 0,
    "tacticalBreakthrough": 0,
    "handOfDeath": 4
  }
}
{
  "id": "1034967155",
  "val": {}
}
{
  "id": "1034967155",
  "val": {
    "crucialShotMedal": 0,
    "prematureDetonationMedal": 0,
    "sentinelMedal": 0,
    "infiltratorMedal": 0,
    "fightingReconnaissanceMedal": 0,
    "fireAndSteelMedal": 0,
    "rangerMedal": 0,
    "reliableComrade": 0,
    "pyromaniacMedal": 0,
    "wolfAmongSheepMedal": 0,
    "heavyFireMedal": 0,
    "bruteForceMedal": 0,
    "guerrillaMedal": 0,
    "promisingFighterMedal": 0,
    "beasthunter": 0,
    "geniusForWarMedal": 0,
    "sinai": 0,
    "pattonValley": 0
  }
}
{
  "id": "1034967155",
  "val": {
    "armorPiercer": 0,
    "aimer": 0,
    "titleSniper": 0,
    "deathTrack": 0,
    "invincible": 0,
    "victoryMarch": 0,
    "EFC2016": 0,
    "diehard": 0,
    "WFC2014": 0,
    "tacticalBreakthrough": 0,
    "handOfDeath": 0
  }
}

How do I now get this into the .csv format I need?

Any assistance would be greatly appreciated - thanks!

jq170727 · Accepted Answer · 2019-10-08 00:09:36Z

You can expand .value again with another application of to_entries[] and then generate csv output with string interpolation. e.g.

.data | to_entries[] | {id: .key, val: (.value[] | to_entries[])} | "\(.id),\(.val.key),\(.val.value)"

Sample execution: with the above command in test.jq and your data in test.json and using head to show only the first ten lines:

$ jq -Mr -f test.jq test.json | head
1019761328,medalCarius,1
1019761328,medalHalonen,3
1019761328,aimer,6
1019761328,invader,13
1019761328,armorPiercer,18
1019761328,medalMonolith,2
1019761328,medalEkins,1
1019761328,medalKay,2
1019761328,duelist,409
1019761328,newMeritPM2,1

For your specific case a more robust way would be to use @csv instead of string interpolation. e.g.

.data | to_entries[] | {id: .key, val: (.value[] | to_entries[])} | [.id, .val.key, .val.value] | @csv

Sample execution

$ jq -Mr -f test.jq test.json | head
"1019761328","medalCarius",1
"1019761328","medalHalonen",3
"1019761328","aimer",6
"1019761328","invader",13
"1019761328","armorPiercer",18
"1019761328","medalMonolith",2
"1019761328","medalEkins",1
"1019761328","medalKay",2
"1019761328","duelist",409
"1019761328","newMeritPM2",1

If you know the id is a number and want the output to reflect that you can use tonumber as well:

.data | to_entries[] | {id: .key, val: (.value[] | to_entries[])} | [(.id|tonumber), .val.key, .val.value] | @csv

Sample execution

$ jq -Mr -f test.jq test.json | head
1019761328,"medalCarius",1
1019761328,"medalHalonen",3
1019761328,"aimer",6
1019761328,"invader",13
1019761328,"armorPiercer",18
1019761328,"medalMonolith",2
1019761328,"medalEkins",1
1019761328,"medalKay",2
1019761328,"duelist",409
1019761328,"newMeritPM2",1

aborruso · Accepted Answer · 2019-10-07 21:00:44Z

in some way you need to apply a wide to long transformation.

You could complete your jq work unsing Miller (https://github.com/johnkerl/miller/issues), and applying this command to your json output

mlr --j2c unsparsify  then reshape -r "val:"  -o item,value then filter -S '$value!=""' then put -S '$item=gsub($item,"val:","")' input.json >output.csv

you will have

id,item,value
1019761328,medalCarius,1
1019761328,medalHalonen,3
1019761328,aimer,6
1019761328,invader,13
1019761328,armorPiercer,18
1019761328,medalMonolith,2
1019761328,medalEkins,1
1019761328,medalKay,2
1019761328,duelist,409
1019761328,newMeritPM2,1
1019761328,readyForBattleLT,4
1019761328,defender,15
1019761328,readyForBattleATSPG,4
1019761328,medalLeClerc,2
1019761328,demolition,112
1019761328,supporter,13
1019761328,steelwall,107
1019761328,medalLehvaslaiho,28
1019761328,medalAbrams,2
1019761328,readyForBattleSPG,4
1019761328,medalPoppel,1
1019761328,medalPascucci,68
1019761328,reliableComrade,303
1019761328,NY19A1,1
1019761328,NY19A2,1
1019761328,tankwomen,1
1019761328,luckyDevil,10
1019761328,NY18A3,1
1019761328,NY18A2,1
1019761328,mainGun,28
1019761328,NY18A1,1
1019761328,sinai,5
1019761328,firstMerit,1
1019761328,medalOrlik,8
1019761328,bonecrusher,824
1019761328,titleSniper,41
1019761328,warrior,5
1019761328,ironMan,130
1019761328,huntsman,2
1019761328,even,35
1019761328,medalKolobanov,1
1019761328,scout,4
1019761328,beasthunter,5
1019761328,kamikaze,30
1019761328,02YearsOfService,1
1019761328,tankExpert2,1
1019761328,tankExpert1,1
1019761328,readyForBattleMT,4
1019761328,tankExpert7,1
1019761328,tankExpert6,1
1019761328,sniper2,10
1019761328,arsonist,106
1019761328,charmed,194
1019761328,medalBillotte,1
1019761328,fighter,147
1019761328,medalLavrinenko,2
1019761328,impenetrable,155
1019761328,sturdy,65
1019761328,NY19A3,1
1019761328,medalKursk,1
1019761328,soldierOfFortune,4
1019761328,handOfDeath,4
1019761328,DdaymarathonMedal,1
1019761328,shootToKill,3029
1019761328,medalDumitru,3
1019761328,evileye,8
1019761328,medalKnispel,1
1019761328,reliableComrade,29
1019761328,sinai,523
1019761328,beasthunter,595
1019761328,crucialShotMedal,0
1019761328,prematureDetonationMedal,0
1019761328,sentinelMedal,0
1019761328,infiltratorMedal,0
1019761328,fightingReconnaissanceMedal,0
1019761328,fireAndSteelMedal,0
1019761328,rangerMedal,0
1019761328,pyromaniacMedal,0
1019761328,wolfAmongSheepMedal,0
1019761328,heavyFireMedal,0
1019761328,bruteForceMedal,0
1019761328,guerrillaMedal,0
1019761328,promisingFighterMedal,0
1019761328,geniusForWarMedal,0
1019761328,pattonValley,62
1019761328,aimer,6
1019761328,armorPiercer,18
1019761328,titleSniper,41
1019761328,handOfDeath,4
1019761328,deathTrack,0
1019761328,invincible,3
1019761328,victoryMarch,0
1019761328,EFC2016,0
1019761328,diehard,6
1019761328,WFC2014,0
1019761328,tacticalBreakthrough,0
1034967155,reliableComrade,0
1034967155,sinai,0
1034967155,beasthunter,0
1034967155,crucialShotMedal,0
1034967155,prematureDetonationMedal,0
1034967155,sentinelMedal,0
1034967155,infiltratorMedal,0
1034967155,fightingReconnaissanceMedal,0
1034967155,fireAndSteelMedal,0
1034967155,rangerMedal,0
1034967155,pyromaniacMedal,0
1034967155,wolfAmongSheepMedal,0
1034967155,heavyFireMedal,0
1034967155,bruteForceMedal,0
1034967155,guerrillaMedal,0
1034967155,promisingFighterMedal,0
1034967155,geniusForWarMedal,0
1034967155,pattonValley,0
1034967155,aimer,0
1034967155,armorPiercer,0
1034967155,titleSniper,0
1034967155,handOfDeath,0
1034967155,deathTrack,0
1034967155,invincible,0
1034967155,victoryMarch,0
1034967155,EFC2016,0
1034967155,diehard,0
1034967155,WFC2014,0
1034967155,tacticalBreakthrough,0

Collectives™ on Stack Overflow

Create a .csv File from JSON Data Using JQ

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related