0

I am downloading api json data and converting into my final result as a csv file.

The sample data is in the below form:

{
    "content": [{
            "Title": "abc",
            "brand": "xyz",
            "information": {
                "c1": "101",
                "c2": "11111",
                "c3": "a,b,c,d,e:abc."
            }
        },
        {
            "Title": "RX100",
            "brand": "Yamaha",
            "information": {
                "c1": "102",
                "c2": "22222",
                "c3": "a."
            }
        },
        {
            "Title": "victor",
            "brand": "TVS",
            "information": {
                "c1": "103",
                "c2": "33333",
                "c3": "a,b,c"
            }
        },
        {
            "Title": "R15",
            "brand": "Yamaha",
            "information": {
                "c1": "104",
                "c2": "44444",
                "c3": "a,b"
            }
        }
    ]
}

I have successfully downloaded and converted into multiple csv on the basis of number of Title. Data looks like in the below form after converting in multiple csv file.

Headers-> c1,c2,c3
csv1-->   101,11111,a,b,c,d,e:abc.
csv2-->   102,22222,a.
csv3-->   103,33333,a,b,c.
csv4-->   104,44444,a,b.

but I want the above data in below form.

Headers-> c1,c2,c3,c4,c5,c6,c7
csv1-->   101,11111,a,b,c,d,e:abc.
csv2-->   102,22222,a.
csv3-->   103,33333,a,b,c.
csv4-->   104,44444,a,b.

Is it possible to divide the c3 into different number of columns on the basis of "," present in my json file using json. The column c3 will get divided on the basis of number of elements are present and will be the maximum value of c3 data.

4

For this exact data,

jq -r '.content[].information | [.c1, .c2, .c3] | join(",")' < sample.json

will work - just inserting commas between the three columns replicates the effect of a joined-upCSV file. For more complex real data, particularly with numbers, this will work better:

jq -r '.content[].information | [.c1, .c2, (.c3|split(",")|.[])] | @csv' < sample.json

This one is all fairly standard up to .c3|split(",")|.[], which

  1. Extracts column 3's value
  2. Splits that value into an array on commas
  3. Flattens that array out

@csv then converts the array into CSV format. This produces the following output for your sample file:

"101","11111","a","b","c","d","e:abc."
"102","22222","a."
"103","33333","a","b","c"
"104","44444","a","b"

If you don't want quotes, the most reliable thing is to use @tsv and just replace the tabs:

jq -r '.content[].information|[.c1, .c2, .c3|split(",")|.[]] | @tsv|gsub("\t"; ",")' < sample.json

If all your values are strings you could also use join(",") again.


If you want the literal header line c1,...c7 created as well, this will do it:

jq -r '[.content[].information|[.c1, .c2, (.c3|split(",")|.[])]] | (([range([.[] | length] | max)|"c" + (.+1|tostring)]|join(",")), (.[] | join(",")))' < sample.json

There's three parts: the first creates the arrays of columns as before, and then the other two parts use that as input:

  1. ([range([.[] | length] | max)|"c" + (.+1|tostring)]|join(",")) generates the header line: it finds the maximum length of any row by generating an array of row lengths, creates a range 0..maximum and maps over it to create an array of "c1".."c7", then joins them all together with commas.
  2. (.[] | join(",")) is analogous to our second half from earlier, and the same things about @csv apply.
  • Thanks @Homer . I will try it and will update about the result. just out of curiosity is it possible to get the data in below format also or it can be done only after converting csv? csv1--> 101,11111,a 101,11111,b 101,11111,c 101,11111,d 101,11111,e:abc csv2--> 102,22222,a csv3--> 103,33333,a 103,33333,b 103,33333,c csv4--> 104,44444,a 104,44444,b – sam Mar 21 at 7:06
  • To remove unnecessary quotes from @csv, one could pass the output through csvformat from the csvkit toolbox. That way, quotes that are needed will be kept. – Kusalananda Mar 21 at 7:58
  • @Homer, I am getting below error during this "jq -r '[.content[].information|[.c1, .c2, .c3|split(",")|.[]]] | (([range([.[] | length] | max)|"c" + (.+1|tostring)]|join(",")), (.[] | join(",")))' < sample.json" . Error : "split input and separator must be strings" – sam Mar 21 at 14:04
  • It sounds like your real data doesn't match the sample data and some of the values are not strings. I've put an extra pair of parentheses in to handle the case where something other than c3 is a non-string, but if the data is heterogeneous to the point that any c3 is not a string you may need a different tack to massage the data first. – Michael Homer Mar 21 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.