Parsing JSON with array

[kanibs]

Hi, I am trying to parse a JSON(given below) using the command line but I do not get the result and indeed the same JSON getting printed. Need some help here if this is possible to parse the given JSON using json2csv and if so, what would be the correct command to do so?

The command I am trying to do this is:

json2csv -i unsupported_json.json --unwind visits.23andme.com,visits.23andme.com.visits -f visits.23andme.com.source_type,visits.23andme.com.visits.date,visits.23andme.com.visits.organic,visits.23andme.com.visits.paid-o traffic.csv

I am mostly interested in the visits element of the JSON and need to unwind the visits.23andme.com.visits for each source_type, i.e there should be two rows in this example for each source_type

{
  "meta": {
    "request": {
      "mtd": false,
      "domain": "23andme.com",
      "country": "US"
    },
    "status": "Success",
    "last_updated": "2022-02-28"
  },
  "visits": {
    "23andme.com": [
      {
        "source_type": "Search",
        "visits": [
          {
            "date": "2022-02-01",
            "organic": 19971.48818781857,
            "paid": 3108.9543638592854
          },
          {
            "date": "2022-02-28",
            "organic": 18002.26573910278,
            "paid": 2883.8879430964184
          }
        ]
      },
      {
        "source_type": "Social",
        "visits": [
          {
            "date": "2022-02-01",
            "organic": 455.49312207251774,
            "paid": 0
          },
          {
            "date": "2022-02-28",
            "organic": 662.624037859397,
            "paid": 0
          }
        ]
      },
      {
        "source_type": "Mail",
        "visits": [
          {
            "date": "2022-02-01",
            "organic": 450.81245455617227,
            "paid": 0
          },
          {
            "date": "2022-02-28",
            "organic": 184.75708073917951,
            "paid": 0
          }
        ]
      }
    ]
  }
}

Thanks.

[kanibs]

hey @juanjoDiaz can you please help me out here?

[juanjoDiaz]

Hi,

Sorry for the late response. I'm quite busy at the moment and won't be able to spend time on this for at least a couple of weeks.

A couple of things at glance:

You are missing a space in front of the -o:

json2csv -i unsupported_json.json --unwind visits.23andme.com,visits.23andme.com.visits -f visits.23andme.com.source_type,visits.23andme.com.visits.date,visits.23andme.com.visits.organic,visits.23andme.com.visits.paid -o traffic.csv

Having a field named 23andme.com is problematic. When you unwind on visits.23andme.com, json2csv tries to unwind a field named com in the field named 23andme in the object named visits. This is:

{
  "visits": {
    "23andme": {
      "com": [/* object to unwind */]
    }
  }
}

You can solved this using the programatic API:

const opts = {
  transforms: [unwind({ paths: [["visits", "23andme.com"], ["visits", "23andme.com", "visits"]] })]
};

but you won't be able to work around this using the CLI.

The same thing applies to the fields and the -f option of the CLI.

Hope this helps! 🙂

[kanibs]

Thanks @juanjoDiaz for finding some time to respond.

This was helpful to get the confirmation and also a workaround which is via programmatic API.