11

I hope someone can guide me how do I convert below json to csv that I'm expecting for.

Much appreciated in advance.

Update: thanks for the solutions provided, but I found that sometimes array does not exist when the 2nd column has only 1 record, example below is "ASite" has only 1 record "unixhost1123" paired to it.

source json

[
  {
    "results": [
      [
        "sm-clust001",
        [
          "163slesm02",
          "163slesm01"
        ]
      ],
      [
        "sm-cssl112",
        [
          "ucsbnchac240",
          "ucsbnchac209",
          "ucsbnchac241",
          "ucsbnchac242"
        ]
      ],
      [
        "ASite",
        "unixhost1123"
      ]
    ]
  }
]

Expecting csv

"sm-clust001","163slesm02"
"sm-clust001","163slesm01"
"sm-cssl112","ucsbnchac240"
"sm-cssl112","ucsbnchac209"
"sm-cssl112","ucsbnchac241"
"sm-cssl112","ucsbnchac242"
"ASite","unixhost1123"
Improve this question

2 Answers 2

Reset to default
6

.[].results[] is a set of arrays. In each array, the first element is what you want to have in the first column, and the second element is another array that we want to loop over.

So let's keep track of the first element in $name (assuming this is a cluster name of some sort), and then output this together with each element of the sub-array:

.[].results[] | .[0] as $name | .[1][]? // .[1] | [ $name, . ] | @csv

The bit that says .[1][]? // .[1] selects the elements of the sub-array if it exists, otherwise it selects the second element of the array (and assumes that it's a scalar instead).

On the command line:

jq -r '.[].results[] | .[0] as $name | .[1][]? // .[1] | [ $name, . ] | @csv' file

Result, given your example document:

"sm-clust001","163slesm02"
"sm-clust001","163slesm01"
"sm-cssl112","ucsbnchac240"
"sm-cssl112","ucsbnchac209"
"sm-cssl112","ucsbnchac241"
"sm-cssl112","ucsbnchac242"
"ASite","unixhost1123"

This solution is generalized for any number of columns in my answer to the user's followup question.

Improve this answer
3
  • 2
    Having to look at jq code, there must be some equivalent to complaining about unreadable APL programs or Perl's regex "line noise". :-)
    – jrw32982
    Aug 25, 2021 at 20:26
  • @jrw32982 Is there some specific thing in my answer that you would want me to explain further?
    – Kusalananda ♦
    Aug 25, 2021 at 20:28
  • 1
    No, your answer is pretty good about explaining the jq constructs you're using. It just struck me that the complaints about Perl regex line noise over the past 10 or 20 years, or comments about impenetrable APL code years ago would equally apply to jq nowadays. It's a very dense, terse syntax in a DSL with implicit looping constructs. And similar to Perl, certain characters (I don't know about APL) are overloaded depending on context, such as jq's "." being used for "this" and also as a prefix for a hash selector.
    – jrw32982
    Aug 26, 2021 at 16:39
4

It's probably not the most elegant of solutions as I'm still learning jq, but this produces your desired output

jq -r '.[].results[] | { a:.[0], b:.[1][] } | [ .a, .b ] | @csv'

Results

"sm-clust001","163slesm02"
"sm-clust001","163slesm01"
"sm-cssl112","ucsbnchac240"
"sm-cssl112","ucsbnchac209"
"sm-cssl112","ucsbnchac241"
"sm-cssl112","ucsbnchac242"

The "magic" is in the second part of the pipeline, { a:.[0], b:.[1][] }, which repeats the first item ([0]) for each one of the second items ([1][]). This can then be converted to a list of arrays and thence output as CSV.

In order to handle the last line of example data you recently added, I've modified the pipeline to convert this outlier into the same shape as the other data. (This is also a learning exercise for me.)

jq -r '
    .[].results[] |                     # Concentrate only on the results[] array
    [ .[0], [ .[1][]? // .[1] ] ] |     # Copy through first element; force the second to be an array
    { a:.[0], b:.[1][] } |              # Iterate across the second element as an array
    [ .a, .b ] |                        # Convert the objects we have just created back into array slices
    @csv                                # Output as CSV, remember the -r flag for jq
'

The addition here is the [ .[0], [ .[1][]? // .[1] ] ] component, which passes through the first element of each block unchanged, and then if the second element is not an array converts it to one. (It unwraps the array, or presents the singleton item, and then wraps the result into an array.)

It has also been pointed out that I could have modified my original code to provide a more elegant solution by using (.[1][]? // .[1]) to contain an if/then expression that either iterates across the array elements as before, or simply provides that second element if it's not an array:

jq -r '.[].results[] | { a:.[0], b:(.[1][]? // .[1]) } | [ .a, .b ] | @csv'

Results of either modification

"sm-clust001","163slesm02"
"sm-clust001","163slesm01"
"sm-cssl112","ucsbnchac240"
"sm-cssl112","ucsbnchac209"
"sm-cssl112","ucsbnchac241"
"sm-cssl112","ucsbnchac242"
"ASite","unixhost1123"

I've been learning a lot from this tutorial, which so far has been one of the better ones I've found.

Improve this answer
6
  • Hooray you saved my day
    – Henry Liu
    Aug 20, 2021 at 9:03
  • Certainly I will do that as your solution does work. I just found out that in my json, there are some cases when .[1] is not an array when it only has 1 record (very annoying), is there any workround to this situation. I use your code but getting jq: error (at <stdin>:25): Cannot iterate over string ("unixhost1123")
    – Henry Liu
    Aug 20, 2021 at 9:31
  • @HenryLiu an extension to my original answer that handles your nonconforming data
    – Chris Davies
    Aug 20, 2021 at 10:43
  • 1
    @Kusalananda thank you. Still learning!
    – Chris Davies
    Aug 20, 2021 at 10:54
  • 1
    And you seem to be doing well. I learned that using a set, as in the b key of { a:.[0], b:.[1][] }, was possible and reproduces the object once for each set member. That's rather neat actually.
    – Kusalananda ♦
    Aug 20, 2021 at 10:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .