Using jq, convert csv file to json, using csv filename as top level object name

user_5472512 0
Viewed 527

I need to convert a bunch of csv files to json, using bash and jq v1.6. (I can't use other scripting languages because of a restriction on what script types are allowed to run on the server). Neither JSON or jq are significant parts of my skillset.

I need to use the csv filename as the json object name, read the csv to get the fieldnames from the header, and do a bit of re-mapping to move "owner" to a list (array?) name.

So, take this:

serverset1.csv

location,category,ip,owner
set1rack1,webserver,127.0.0.1,Customer1
set1rack2,appserver,127.0.0.2,Customer1
set1rack1,webserver,127.0.0.3,Customer2
set1rack2,appserver,127.0.0.4,Customer2

to this

[
   {
      "serverset1":[
         {
            "Customer1":[
               {
                  "location":"set1rack1",
                  "category":"webserver",
                  "address":"127.0.0.1"
               },
               {
                  "location":"set1rack2",
                  "category":"appserver",
                  "address":"127.0.0.2"
               }
            ],
            "Customer2":[
               {
                  "location":"set1rack1",
                  "category":"webserver",
                  "address":"127.0.0.3"
               },
               {
                  "location":"set1rack2",
                  "category":"appserver",
                  "address":"127.0.0.4"
               }
            ]
         }
      ]
   }
]

I got as far as this:

filename="serverset1.csv"; \
cat "$filename" | jq --slurp --raw-input --raw-output \
'
  split("\n") | .[1:] | map(split(",")) |
    map(
         {
            "location": .[0],
            "category": .[1],
            "address": .[2],
            "owner": .[3]
         }
    )
'

which yields this:

[
  {
    "location": "set1rack1",
    "category": "webserver",
    "address": "127.0.0.1",
    "owner": "Customer1"
  },
  {
    "location": "set1rack2",
    "category": "appserver",
    "address": "127.0.0.2",
    "owner": "Customer1"
  },
  {
    "location": "set1rack1",
    "category": "webserver",
    "address": "127.0.0.3",
    "owner": "Customer2"
  },
  {
    "location": "set1rack2",
    "category": "appserver",
    "address": "127.0.0.4",
    "owner": "Customer2"
  }
]

but then I hit a syntax wall.

Any help is appreciated.

~Jaimie

jq csv bash json
2 Answers

Use input_filename to get the input filename. To skip the first line, use inputs without the -n option:

jq -R '
  inputs
  | [splits(", *")] as $row
  | {(input_filename): { ($row[3]): $row[:3] }}
' serverset1.csv

This produces a stream of the "filename" objects, which you can then readily assemble into an array by modifying the script (rather than invoking jq a second time).

If the input file has no header, then use the -n command-line option.

Caveats

  1. jq can read very simple CSV files but is not well-suited to reading CSV files in general. If your CSV file cannot easily be read properly by jq, you could use one of the many excellent alternatives for converting a CSV to a TSV file.

  2. The above program does not attempt to convert numeric or boolean strings to JSON numbers or boolean values; any such conversions can however easily be accomplished using jq by making the appropriate tweaks to the above program.

user_9973580

Given the sample input, the following produces the requested output:

# The -n option is omitted in order to skip the header
jq -R '
  reduce inputs as $in ({};
   (input_filename | sub("[.]csv$";"")) as $file
   | ($in | [splits(", *")]) as $row
   | {location: $row[0], category: $row[1], address: $row[2]} as $obj
   | .[$file][$row[3]] += [$obj])
| [ . ]
' serverset1.csv

Additional files can be specified on the command line.

The above could easily be modified if it is desired to generate the key names based on the header.

user_9973580
Related