Yrurtj

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.

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.