Merging JSON fields with Miller

[lolbat]

Hi folks

I just found Miller. Oddly it was the on Hacker News the day after I was looking for something like this.

I am currently working on a web-app that loads a series of JSON files that I have created from CSV export from a spreadsheet. I am currently hand-editing the JSON files to take a series of fields and then add them to a new JSON key that has an array of those files.

So in each JSON record there are the following fields

  "activation": 4,
  "move": 5,
  "fight": 3,
  "shoot": 0,
  "defence": 11,
  "combatDice": 2,
  "health": 2,
  "points": 165,

and I want to change that to

      "stats": {
        "activation": 4,
        "move": 5,
        "fight": 3,
        "shoot": 0,
        "defence": 11,
        "combatDice": 2,
        "health": 2,
        "points": 165
      }

So create a new field called stats, append the fields to it and then remove the final , in the list to ensure that the JSON is valid.

Is there a way to do that in Miller or will I need to use something else? I checked out put but that seems to compute values and not just append them or am I wrong?

TIA

[lolbat]

Hi folks

Just thought that I would pop back in and mention that I solved this on my own. I think I was just trying to use MIller in a way that it isn't suited and I found a different csv tool that is specifically written to work with JSON.

I will be keeping up on releases since I know I will have more work in the future that will require Miller. Thanks to the team for putting this tool out

[aborruso]

I found a different csv tool that is specifically written to work with JSON.

what's the tool?

In Miller if you have this json

[
  {
    "activation": 4,
    "move": 5,
    "fight": 3,
    "shoot": 0,
    "defence": 11,
    "combatDice": 2,
    "health": 2,
    "points": 165
  },
  {
    "activation": 14,
    "move": 53,
    "fight": 35,
    "shoot": 0,
    "defence": 111,
    "combatDice": 22,
    "health": 23,
    "points": 1645
  }
]

and run

mlr --json --jvstack --jlistwrap  rename -r '(.+),stats:\1' input.json

you will have

[
  {
    "stats": {
      "activation": 4,
      "move": 5,
      "fight": 3,
      "shoot": 0,
      "defence": 11,
      "combatDice": 2,
      "health": 2,
      "points": 165
    }
  },
  {
    "stats": {
      "activation": 14,
      "move": 53,
      "fight": 35,
      "shoot": 0,
      "defence": 111,
      "combatDice": 22,
      "health": 23,
      "points": 1645
    }
  }
]

The default string used for key concatenation is : and I use rename verb to put stats: at the beginning of each field name.

[johnkerl]

Hi @lolbat -- in Miller 6 you can do

$ cat example.json
{
  "color": "yellow",
  "shape": "triangle",
  "flag": true,
  "k": 1,
  "index": 11,
  "quantity": 43.6498,
  "rate": 9.8870
}
{
  "color": "red",
  "shape": "square",
  "flag": true,
  "k": 2,
  "index": 15,
  "quantity": 79.2778,
  "rate": 0.0130
}
{
  "color": "red",
  "shape": "circle",
  "flag": true,
  "k": 3,
  "index": 16,
  "quantity": 13.8103,
  "rate": 2.9010
}

$ mlr -j --from example.json put '$stats={"index":$index,"quantity":$quantity,"rate":$rate}; unset $index,$quantity,$rate'
{
  "color": "yellow",
  "shape": "triangle",
  "flag": true,
  "k": 1,
  "stats": {
    "index": 11,
    "quantity": 43.6498,
    "rate": 9.8870
  }
}
{
  "color": "red",
  "shape": "square",
  "flag": true,
  "k": 2,
  "stats": {
    "index": 15,
    "quantity": 79.2778,
    "rate": 0.0130
  }
}
{
  "color": "red",
  "shape": "circle",
  "flag": true,
  "k": 3,
  "stats": {
    "index": 16,
    "quantity": 13.8103,
    "rate": 2.9010
  }
}

Or, more flexibly:

mlr -j --from example.json put '
  begin {
    @stats_fields = ["index", "quantity", "rate"]
  }
  $stats = {};
  for (stats_field in @stats_fields) {
    $stats[stats_field] = $[stats_field];
    unset $[stats_field];
  }
'

with the same output.