How to display all the values of the multi-value column(array-like) in different rows?

[kasis152]

I have the raw data in format :
{"col1":"1",{col2":"2"},{.........(continue)
which if I have to visualize using https://codebeautify.org/string-to-json-online :

Object{1}
  ->a{4}
     col1: 1
     col2: 2
     col3: 3
     col4: 4
  ->b[3]
     ->0{3}
        col5:"5
        col6[0]
           0:6
     ->1{3}
        col5: "55"
        col6[1]
          0:66
      ->2{3}
        col5: 55
        col6[1]
          0:666

 And if my Splunk query is like 
index="api"
| rename a.col1 as "col1",a.col2 as "col2", b{}.col5 as "col5", b{}.col6{} as "col6"
| table "col1","col2","col5","col6"

it display me:

col1	col2	col5	col6
1	2	5 55	6 66 666

Moreover , if I export it in csv
It only shows me first value of array(multi-value)

col1	col2	col3	col4
1	2	5	6

but should be like : (each row 1:1 mapped)
MY DESIRED TABLE

col1	col2	col5	col6
1	2	5	6
1	2	55	66
1	2	55	666

[kasis152]

Appreciate your help

[richgalloway]

It would help if you showed the raw data rather than some strange pretty-printed version of it.  We can't test solutions with the string-to-json-online output.

I believe there is more to the query than is shown because what is shown won't produce the given output.  I suspect there is a stats values(...) command being used and that's why only one 55 appears in the col5 field.  If you replace values() with list() then the values list won't be deduplicated.  Then you can expand col5 and col6 after first zipping them together.

| stats list(col5) as col5, list(col6) as col6 by col1,col2
| eval col56=mvzip(col5, col6) 
| mvexpand col56
| eval col56=split(col56, ",") 
| eval col5=mvindex(col56,0), col6=mvindex(col56,1)
| table col1 col2 col5 col6

This presumes col5 and col6 will always have the same number of values.  If that's not the case then another solution is needed.

[kasis152]

@richgalloway
Yes you were right , Raw data might help u better answer my question .
but your query gave me such better shape .
my visualization was kind of off .

Can you help me rewrite the query , given that objective is still the save to obtain the same desired output as above.

Correction:
My raw data : 
{"col1":"1","col2":"2","col3":"3","col4":"4","b":[{"col5":"5","col6":["6"]},{"col5":"55","col6":["66","666"]}]}

[richgalloway · Accepted solution]

I don't have a way to produce the format you seek, but I can combine the values so they should all export.  Perhaps Excel offers a way to break them up.

| makeresults 
| eval _raw="{\"col1\":\"1\",\"col2\":\"2\",\"col3\":\"3\",\"col4\":\"4\",\"b\":[{\"col5\":\"5\",\"col6\":[\"6\"]},{\"col5\":\"55\",\"col6\":[\"66\",\"666\"]}]}" | spath
| rename b{}.col5 as "col5", b{}.col6{} as "col6"
| eval col5=mvjoin(col5,", "), col6=mvjoin(col6, ", ")
| table col1 col2 col5 col6

Stepping back a bit.  What problem are you trying to solve with Excel?  Perhaps a native Splunk report/dashboard can do the job.

[kasis152]

The ultimate goal is to have the Dashboard in Tableau .
Hence, wanted a formatted data.

[richgalloway]

Tableau can access data in Splunk using ODBC (at least it could - I heard rumors that's not possible with some versions of Tableau).  With ODBC, Tableau launches a saved search that runs on Splunk and the results are automatically exported to Tableau.  It's up to the saved search to format the output, unless Tableau can perform additional transformations.  It's been several years since I used Tableau so that's about all the help I can offer there.