csv to json or json format as column array command line

Laurent Felix 0
Viewed 38

is it possible to obtain this output ? With a command line tool (jq or other)

From csv

id,age,gender
1,39,M
2,25,M

To json column array

[{"id":["1","2"]},{"age":["39","25"]},{"gender":["M","M"]}]

OR from json

[
    {
        "id": "1",
        "age": "39",
        "gender": "M"
    },
    {
        "id": "2",
        "age": "25",
        "gender": "M"
    }
]

To json column array

[{"id":["1","2"]},{"age":["39","25"]},{"gender":["M","M"]}]

Hoping you understood me. Thank you in advance for your answers.

jq command-line csv linux json
1 Answers

There is no cannonical way to read in CSV as the format is not standardized. Existing usages mostly differ in the field separator and the escape sequence (used if either one happens to be part of the data), which you would have to consider explicitly when reading in raw text manually.

Therefore, I regard it as safer to follow your second approach. Here's one way for the given input:

[
    {
        "id": "1",
        "age": "39",
        "gender": "M"
    },
    {
        "id": "2",
        "age": "25",
        "gender": "M"
    }
]

jq 'map(to_entries) | transpose | map({(first.key): map(.value)}) | add'

{
  "id": [
    "1",
    "2"
  ],
  "age": [
    "39",
    "25"
  ],
  "gender": [
    "M",
    "M"
  ]
}

Demo

pmf0
Related