Flattening JSON API response in Node

Question

JSON Object returned from API Response:

{3 items
    "total_reviews":574
    "stars_stat":{5 items
    "1":"2%"
    "2":"1%"
    "3":"3%"
    "4":"8%"
    "5":"85%"
    }
    "result":[10 items
    0:{9 items
    "id":"R3BWKTLO7CM3Y9"
    "asin":{...}2 items
    "review_data":"Reviewed in the United States on April 15, 2022"
    "date":{2 items
    "date":"April 15, 2022"
    "unix":1649988000
    }
    "name":"Sally N."
    "rating":5
    "title":"Great alcohol !"
    "review":"Great products"
    "verified_purchase":true
    }
    1:{...}9 items
    2:{...}9 items
    3:{...}9 items
    4:{...}9 items
    5:{...}9 items
    6:{...}9 items
    7:{...}9 items
    8:{...}9 items
    9:{...}9 items
    ]

I've tried various approaches but keep getting stuck implementing the approach or realizing it's wrong so I just commented out all the stuff I tried. I first tried to just select for a specific information in the response object and store it in a variable, then passing the info to a record object that matches the CSV headers I need, so I can write it to a file, however, couldn't make it work iteratively in a loop for the different key/value pairs I need. Then I read about an inbuilt json object function that lets you loop through a json object based on the keys object.keys, values object.values, or both object.entries, but I have nested key/values and some of the information I need is nested. So I think the solution is to figure out how to flatten the JSON object into just the key-values I need, and that is what I'm seeking help with. Ultimately trying to write this info to a csv file.

I need the following fields: customer name, product name, SKU, review content, star rating, and time and date created

My Code I've tried:

const axios = require("axios");
const converter = require("json-2-csv");
const fs = require("fs");
const moment = require('moment');
const createCsvWriter = require('csv-writer').createObjectCsvWriter;

const csvWriter = createCsvWriter({
  path: './ProductReviews.csv',
  header: ['asin', 'name', 'review', 'rating'].map((item) => ({ id: item, title: item }))
})

const options = {
  method: 'GET',
  url: 'https://amazon23.p.rapidapi.com/reviews',
  params: {asin: 'B00DUGMVC6', sort_by: 'recent', page: '1', country: 'US'},
  headers: {
    'X-RapidAPI-Key': 'redacted',
    'X-RapidAPI-Host': 'redacted'
  }
};


    axios.request(options).then(function (response) {
        console.log(response.data);
      // for (let i = 0; i < 10; i++  ) {
      // let name = response.data.result[i].name;
      // let content = response.data.result[i].review;
      // let rating = response.data.result[i].rating;
      // let date = response.data.result[i].date.date
      
      // let momentvar = moment(date, 'MMM DD, YYYY').format();
    
      // const records = [
      //   {
      //     customername: name, productname: 'Isopropyl Alcohol 99.9%', sku: 'B00DUGMVC6', reviewcontent: content, starrating: rating, timeanddate: date
      //   }
      // ]
    
      // for (let [key, value] of Object.entries(response.data)) {
      //   console.log(key, value);
      // }    
    
        // try { 
        //    csvWriter.writeRecords(response.data.result[0]);
        // } catch (error) {
        //   console.log(error);
        // }
    
        // converter.json2csv(response.data, (err, csv) => {
        //     if (err) {
        //       throw err;
        //     }
    
        //     fs.writeFileSync('todos.csv', csv);
        // })
    
      // csvWriter.writeRecords(records)       // returns a promise
      //   .then(() => {
      //       console.log('...Done');
      //   });
        
      // }
    
      // var response = JSON.parse(response);
      // console.log(response);
    
      // csvWriter.writeRecords(response.data)
    
        // converter.json2csv(response.data, (err, csv) => {
        //     if (err) {
        //         throw err;
        //     }
            
        //     console.log(csv);
        // })
    
    }).catch(function (error) {
        console.error(error);
    });

Answer 1

Just skimming css-writer documentation, it looks like the package wants two js objects: one describing the header, and another array of records who's keys match the ids given in the header object.

From what I can make of the OP's input object, the transform is straight-forward. The only unclear bit is what should be written for the asin key, which is described in the question as an object, but not elaborated further.

Here's a demo of the transform to csv-writer params. In it, we just stringify the asin object...

const data = {
  "total_reviews":574,
  "stars_stat":{
    "1":"2%",
    "2":"1%",
    "3":"3%",
    "4":"8%",
    "5":"85%"
  },
  "result":[
    {
      "id":"R3BWKTLO7CM3Y9",
      "asin":{ 
        "original":"B00DUGMVC6",
        "variant":"B00DUGMVC6"
      }, 
      "review_data":"Reviewed in the United States on April 15, 2022",
      "date":{
        "date":"April 15, 2022",
        "unix":1649988000
      },
      "name":"Sally N.",
      "rating":5,
      "title":"Great alcohol !",
      "review":"Great products",
      "verified_purchase":true
    },
    // more like this
  ]
}

const result = data.result;
// OP should replace the previous line with const result = response.data.result;


const header = ['asin', 'name', 'review', 'rating'].map((item) => ({ id: item, title: item }))
const records = result.map(({ asin: { original, variant } , name, date: { date }, review, rating }) => {
  date = new Date(date).toISOString();
  return { original, variant, name, review, rating, date };
});

console.log(header, records);

// // OP should replace the previous line with:
// const csvWriter = createCsvWriter({
//   path: './ProductReviews.csv',
//   header: header
// })
// csvWriter.writeRecords(records)

edit Calling and writing several pages might look something like the following. Notes: (1) from reading, I learned that asin is like a product id, probably invariant over all reviews, (2) reasonably low volume (see if it performs, then consider streaming if it doesn't), (3) guessing that no records returned for a page means we're done.

async function getReviewPage(asin, page, csvWriter) {
  const options = {
    method: 'GET',
    url: 'https://amazon23.p.rapidapi.com/reviews',
    params: {asin, sort_by: 'recent', page: page+'', country: 'US'},
    headers: {
      'X-RapidAPI-Key': 'redacted',
      'X-RapidAPI-Host': 'redacted'
    }
  };
  const response = await axios.request(options);
  const result = response.data.result;
  
  const records = result.map(({ asin: { original, variant } , name, date: { date }, review, rating }) => {
    date = new Date(date).toISOString();
    return { original, variant, name, review, rating, date };
  });
  if (records.length) await csvWriter.writeRecords(records)
  return records.length === 0; 
}

async function getAllReviews(asin) {
  const header = ['asin', 'name', 'review', 'rating'].map((item) => ({ id: item, title: item }))
  const csvWriter = createCsvWriter({
    path: './ProductReviews.csv',
    header: header
  });
  let done = false;
  let page = 1;
  while (!done) {
    done = await getReviewPage(asin, page++, csvWriter);
  }
  return page-1
}

// test it with
getAllReviews('myASIN').then(pagesWritten => console.log(`wrote ${pagesWritten} pages`);

// run it one of two ways:
async function getAllReviewsForAllProducts(asinArray) {
  // in parallel, though you might get throttled by the service...
  return await Promise.all(asinArray.map(getAllReviews));

  // OR sequentially
  for (let asin of asinArray) {
    await getAllReviews(asin)
  }
}