CODEWITHSUNDEEP
jsonobjectexport-to-csvjq
Marilu
Marilu

Reputation: 392

Convert json to csv / jq Cannot iterate over string

[
{
    "Description": "Copied for Destination xxx from Sourc 30c for Snapshot 1. Task created on X,52,87,14,76.",
    "Encrypted": false,
    "ID": "snap-074",
    "Progress": "100%",
    "Time": "2019-06-11T09:25:23.110Z",
    "Owner": "883065",
    "Status": "completed",
    "Volume": "vol1",
    "Size": 16
},
{
    "Description": "Copied for Destination yy from Source 31c for Snapshot 2. Task created on X,52,87,14,76.",
    "Encrypted": false,
    "ID": "snap-096",
    "Progress": "100%",
    "Time": "2019-06-11T10:18:01.410Z",
    "Owner": "1259",
    "Status": "completed",
    "Volume": "vol-2",
    "Size": 4
}

]

I have that json file that I'm trying to convert to csv using the following command:

     jq -r '. | map(.Description[], .Encrypted, .ID, .Progress, .Time, .Owner, .Status, .Volume, .Size | join(",")) | join("\n")' snapshots1.json

But I'm getting error:

jq: error (at snapshots1.json:24): Cannot iterate over string ("Copied for...)

I look at similar post in jq: error: Cannot iterate over string but can't figure out the error. Any help is appreciated.

Upvotes: 2

Views: 3789

Answers (3)

Marilu
Marilu

Reputation: 392

jq -r '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv' snapshots1.json >> myfile.csv

Found this post that explains this code and it worked for me.

Upvotes: 1

peak
peak

Reputation: 116870

There's a risk in using .. here to extract the "values" in an object: what if the ordering of the keys in the input objects differs between objects?

Here's a generic filter which addresses this and other issues. It also emits a suitable "header" line:

def object2array(stream):
  foreach stream as $x (null;
    if . == null then $x | [true, keys_unsorted] else .[0]=false end;
    (if .[0] then .[1] else empty end),
    .[1] as $keys | $x | [getpath( $keys[] | [.]) ] );

Example

def data: [{a:1,b:2}, {b:22,a:11,c:0}];

object2array(data[])

produces:

["a","b"]
[1,2]
[11,22]

Just right for piping to @csv or @tsv.

Solution

So the solution to the original problem would essentially be:

object2array(.[]) | @csv

Upvotes: 0

dev.null
dev.null

Reputation: 11

I think you were on the right track. Here is how I'd do it:

jq -r '.[] | map(..) | @csv' snapshot1.json > snapshot1.csv

There's a couple of small problems with your code:

  1. .Descriptions[] - Descriptions doesn't have an array so the square brackets don't work - there's no array to open.
  2. Suppose we get rid of the square brackets, you see that the code works insofar as it puts the contents of the objects into an array. However, it put the contents into one array - the result is that your csv will only have one line (and I'm assuming that you want each object on separate rows.). This is because the map function puts all the contents into one array (see documentation: jq Manual) - so you have to split open the array first.
  3. The first part of your code with the dot (.) doesn't do anything - it simply returns the whole JSON as is. If you want play around with it, try .[] and then experiment from there.

Edited: Spelling

Upvotes: 0

Related Questions