Bash store JSON output to CSV

Question

I have a bash script in which I am trying to store the aws command output (which is a JSON) to CSV file. This is the JSON output of the aws s3 command.

{
  "Expiration": {
    "Days": 7
  },
  "ID": "Expire after 7 days",
  "Filter": {
    "Prefix": ""
  },
  "Status": "Enabled"
}
{
  "Expiration": {
    "Days": 1
  },
  "ID": "Remove after 1 day",
  "Filter": {},
  "Status": "Enabled"
}

Here is my script

#!/bin/bash
y="Rule"
x=$(aws s3api get-bucket-lifecycle-configuration --bucket test | jq -r '.Rules[]')
echo  ${y}, ${x} >> sample.csv

The JSON response is getting saved to sample.csv but in separate columns. I want it to be in 1 column and in pretty JSON format.

Is there a way to do this?

@Barmar using the aws s3 command I got the above JSON response from "Rules" field — esahmo, Jan 07 '22 at 21:27
The JSON has nested objects, but CSV is flat. What do you expect the CSV to be like? — Barmar, Jan 07 '22 at 21:28
@Barmar I just want the full JSON to come in 1 column. If you see in the image its coming in multiple columns. — esahmo, Jan 07 '22 at 21:32
You need to quote the result, otherwise the `,` in the JSON will be field delimiters. — Barmar, Jan 07 '22 at 21:33
So you need to put double quotes around the JSON, and escape all the nested double quotes. — Barmar, Jan 07 '22 at 21:33
Do you need Bash? A language w/ support for JSON & CSV would be vastly easier. PHP, Python, JS, etc, etc... — quickshiftin, Jan 07 '22 at 21:37
@quickshiftin currently it has to be done in bash. If it gets more complicated will move to Python — esahmo, Jan 07 '22 at 21:41
by the time you try to parse json and write a csv, it will already be complicated enough for python imo. — quickshiftin, Jan 07 '22 at 21:42
Please add ouput of your `aws` command to your question (no comment here). — Cyrus, Jan 07 '22 at 21:51
Oh, as the JSON isn't valid I thought that you wanted to generate a CSV with a rule per line, that is not the case? — Fravadona, Jan 08 '22 at 00:58
@esahmo, it looks like you've been around here long enough, and asked enough questions, to know [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) -- show what your desired output is. — glenn jackman, Jan 08 '22 at 03:00

Fravadona · Answer 1 · 2022-01-08T09:42:58.640

Update

I may have misunderstood what OP wanted. If it's a CSV with only one record then you can do:

y=Rule
x=$(aws s3api get-bucket-lifecycle-configuration --bucket test | jq '.Rules[]')
jq -nr --arg y "$y" --arg x "$x" '[$y, $x] | @csv' > sample.csv

Please take notice that the JSON stored in the second column of the CSV is not strictly valid as it's composed of a stack of objects instead of an array.

Prior answer

You can use jq:

aws s3api get-bucket-lifecycle-configuration --bucket test |
jq -r '.Rules[] | [ (. | tostring) ] | @csv'

CSV output:

"{""Expiration"":{""Days"":7},""ID"":""Expire after 7 days"",""Filter"":{""Prefix"":""""},""Status"":""Enabled""}"
"{""Expiration"":{""Days"":1},""ID"":""Remove after 1 day"",""Filter"":{},""Status"":""Enabled""}"

But as @ZachYoung pointed out, as of now jq doesn't have any easy way to generate pretty strings with tostring or tojson.

Personally I would stick to the compact version, but if the pretty formatting is really necessary then I would switch to ruby instead of jq (python is also an option but it lacks the one-liner thingy^^):

aws s3api get-bucket-lifecycle-configuration --bucket test |
ruby -rjson -rcsv -e 'puts "Rules", JSON.parse(ARGF.read)["Rules"].map{|rule| [JSON.pretty_generate(rule)].to_csv}'

CSV output:

Rules
"{
  ""Expiration"": {
    ""Days"": 7
  },
  ""ID"": ""Expire after 7 days"",
  ""Filter"": {
    ""Prefix"": """"
  },
  ""Status"": ""Enabled""
}"
"{
  ""Expiration"": {
    ""Days"": 1
  },
  ""ID"": ""Remove after 1 day"",
  ""Filter"": {
  },
  ""Status"": ""Enabled""
}"

Good try, but OP said they wanted the JSON "pretty printed". (+1 still!) — Zach Young, Jan 07 '22 at 22:27
@Fravadona are you suggesting to change the aws command to this ? `bRules=$(aws s3api get-bucket-lifecycle-configuration --bucket test | jq -r '.Rules[]' | tostring | @csv)` — esahmo, Jan 07 '22 at 22:35
@esahmo, that would be the simplest IMO: `bRules=$(aws s3api get-bucket-lifecycle-configuration --bucket test | jq -r '.Rules[] | [ (. | tostring) ] | @csv')` but it all depends on how you'll use the `bRules` variable — Fravadona, Jan 07 '22 at 22:41
On the money! Wish I could upvote again for getting the first output in a variable and using that again in another jq template: that was exactly the trickery I called out not knowing :) — Zach Young, Jan 08 '22 at 02:28

Bash store JSON output to CSV

1 Answers1

Update

Prior answer