csv to json or json format as column array command line

Question

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.

Does this answer your question? [How to convert arbitrary simple JSON to CSV using jq?](https://stackoverflow.com/questions/32960857/how-to-convert-arbitrary-simple-json-to-csv-using-jq) — 0stone0, Sep 23 '22 at 09:42
What did you try? What tools did you already consider and how did you fail? — Inian, Sep 23 '22 at 09:42

score 1 · Accepted Answer · answered Sep 23 '22 at 09:55

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

@oguz_ismail - Please undelete your useful answer, though I'd suggest adding a `caveat emptor`. — peak, Sep 23 '22 at 20:33

csv to json or json format as column array command line

1 Answers1