Converting nested JSON to CSV for simplified data organization

I need help flattening JSON in order to parse it as a CSV. The current flattening process is not working correctly, as the customer.addresses field is being filled with 'addresstype: r' and then skipping all other fields such as city, countrycode, countycode, etc. before moving on to customer.companyName. It seems like my JavaScript code needs some adjustment to properly flatten the nested JSON so that it can be displayed correctly in Excel. Any assistance with this issue would be greatly appreciated.

JSON (this is just a portion of the nested json and could vary in depth)

[
  {
    "countyCode": 12,
    "customer": {
      "addresses": [
        {
          "addressType": "R",
          "city": "BRADENTON",
          "countryCode": "US",
          "countyCode": 12,
          "foreignPostalCode": null,
          "state": "FL",
          "streetAddress": "819 15th Ave Dr E",
          "zipCode": 34211,
          "zipPlus": null
        },
        {
          "addressType": "M",
          "city": "BRADENTON",
          "countryCode": "US",
          "countyCode": 12,
          "foreignPostalCode": null,
          "state": "FL",
          "streetAddress": "PO BOX 124",
          "zipCode": 34201,
          "zipPlus": 0124
        }
      ],
      
      --- Rest of the JSON data ---

JS

--- JavaScript functions for flattening objects ---

...functions here...
  
  function flatten(object, addToList, prefix) {
    Object.keys(object).map(key => {
      if (object[key] === null) {
        addToList[prefix + key] = "";
      } else
      if (object[key] instanceof Array) {
        addToList[prefix + key] = listToFlatString(object[key]);
      } else if (typeof object[key] == 'object' && !object[key].toLocaleDateString) {
        flatten(object[key], addToList, prefix + key + '.');
      } else {
        addToList[prefix + key] = object[key];
      }
    });
    return addToList;
  }

Once I have defined these JavaScript functions, I run the JSON string through them using the following method:

// Run the JSON string through the flattening utilities above
          var flatJSON = JSON.parse(evt.target.result).map(record => flatten(record, {}, ''));

          var csv = Papa.unparse(flatJSON);

Answer №1

If you need to address the issues in your current code, consider using the following function provided below.

function parseNestedObject(obj, result, prefix) {
    Object.keys(obj).map(key => {
        if (obj[key] === null) {
            result[prefix + key] = "";
        } else
        if (obj[key] instanceof Array) {
            for (i in obj[key]) {
                parseNestedObject(obj[key][i], result, prefix + key + "." + i)
            }
        } else if (typeof obj[key] == 'object' && !obj[key].toLocaleDateString) {
            parseNestedObject(obj[key], result, prefix + key + '.');
        } else {
            result[prefix + key] = obj[key];
        }
    });
    return result;
}

The function above replaces the problematic line with a loop that recursively handles nested objects and arrays, producing the desired output based on the sample JSON you have provided.

[ '0.countyCode': 12,
  '0.excludedFlag': '',
  ...
  // Truncated content for brevity
  '2.vehicle.yearMake': 2006 ]

Answer №2

If you need to process nested objects in JavaScript, you can utilize a function like the one provided below:

data = require("./data.json")

flattenObject = (obj) => {
    let flattenKeys = {};
    for (let i in obj) {
        if (!obj.hasOwnProperty(i)) continue;
        if ((typeof obj[i]) == 'object') {
            let flatObject = flattenObject(obj[i]);
            for (let j in flatObject) {
                if (!flatObject.hasOwnProperty(j)) continue;
                flattenKeys[i + '.' + j] = flatObject[j];
            }
        } else {
            flattenKeys[i] = obj[i];
        }
    }
    return flattenKeys;
}

console.log(flattenObject(data))

The resulting flattened object derived from your array's first element is displayed below:

{ countyCode: 12,
  'customer.addresses.0.addressType': 'R',
  'customer.addresses.0.city': 'BRADENTON',
  'customer.addresses.0.countryCode': 'US',
  'customer.addresses.0.countyCode': 12,
  'customer.addresses.0.state': 'FL',
  'customer.addresses.0.streetAddress': '819 15th Ave Dr E',
  'customer.addresses.0.zipCode': 34211,
  'customer.addresses.1.addressType': 'M',
  'customer.addresses.1.city': 'BRADENTON',
  'customer.addresses.1.countryCode': 'US',
  'customer.addresses.1.countyCode': 12,
  'customer.addresses.1.state': 'FL',
  'customer.addresses.1.streetAddress': 'PO BOX 124',
  'customer.addresses.1.zipCode': 34201,
  'customer.addresses.1.zipPlus': '124',
  'customer.customerNumber': 932874,
  'customer.customerStopFlag': false,
  'customer.customerType': 'I',
  'customer.dateOfBirth': '1936-08-05T00:00:00',
  'customer.dlExpirationDate': '2022-08-05T00:00:00',
  'customer.dlRenewalEligibilityFlag': true,
  'customer.driverLicenseNumber': 'B360722339284',
  'customer.firstName': 'David',
  'customer.lastName': 'Brierton',
  'customer.middleName': 'Hugh',
  'customer.sex': 'M' }

Answer №3

Here's an alternate approach:

function convertObjectToString(obj) {
    var path = [],
        nodes = {},
        parseObj = function (obj) {
            if (typeof obj == 'object') {
                if (obj instanceof Array) { //array
                    for (var i = 0, l = obj.length; i < l; i++) {
                        path.push(i);
                        parseObj(obj[i]);
                        path.pop();
                    }
                }
                else {  //object
                    for (var node in obj) {
                        path.push(node);
                        parseObj(obj[node]);
                        path.pop();
                    }
                }
            }
            else {  //value
                nodes[path.join('_')] = obj;
            }
        };

    parseObj(obj);
    return nodes;
}

console.log(JSON.stringify(convertObjectToString(data)));
  • I utilized a global path array to streamline the recursive path and reduce unnecessary string operations
  • I took into consideration the potential size of JSON files to ensure efficiency
  • In this specific case, there is no requirement to check for "hasOwnProperty"

Answer №4

If you're looking for an efficient solution, I suggest utilizing the flat package instead of creating a new one from scratch. If you prefer to explore existing code, you can refer to Hugh's implementation found here. He covers both flatten and unflatten functionalities. Here is a snippet of the flatten function:

var isBuffer = require('is-buffer')

module.exports = flatten
flatten.flatten = flatten
flatten.unflatten = unflatten

function flatten (target, opts) {
  opts = opts || {}

  var delimiter = opts.delimiter || '.'
  var maxDepth = opts.maxDepth
  var output = {}

  function step (object, prev, currentDepth) {
    currentDepth = currentDepth || 1
    Object.keys(object).forEach(function (key) {
      var value = object[key]
      var isarray = opts.safe && Array.isArray(value)
      var type = Object.prototype.toString.call(value)
      var isbuffer = isBuffer(value)
      var isobject = (
        type === '[object Object]' ||
        type === '[object Array]'
      )

      var newKey = prev
        ? prev + delimiter + key
        : key

      if (!isarray && !isbuffer && isobject && Object.keys(value).length &&
        (!opts.maxDepth || currentDepth < maxDepth)) {
        return step(value, newKey, currentDepth + 1)
      }

      output[newKey] = value
    })
  }

  step(target)

  return output
}

Answer №5

Provided below is a solution that ensures readability and accurately parses dates in addition to flattening objects recursively. While several solutions flatten objects recursively, they often overlook the fact that dates are also objects. This oversight may not be an issue when working with JSON files as mentioned by OP, but using the same flattener-function on JavaScript objects could lead to incorrect parsing of dates.

The 'flatten' function inserts one or more prefixes if necessary (e.g., "my.prefixes.here.0.value")

export const flatten = (obj, prefix = [], current = {}) => {
  const isDate = obj instanceof Date;
  const isObject = typeof obj === "object";
  const notNull = obj !== null;
  const flattenRecursive = !isDate && isObject && notNull;

  if (flattenRecursive) {
    for (const key in obj) {
      flatten(obj[key], prefix.concat(key), current);
    }
  } else {
    current[prefix.join(".")] = obj;
  }
  return current;
};

Edit: Output from the function applied to example data provided by OP:

{ '0.countyCode': 12,
  '0.customer.addresses.0.addressType': 'R',
  '0.customer.addresses.0.city': 'BRADENTON',
  '0.customer.addresses.0.countryCode': 'US',
  '0.customer.addresses.0.countyCode': 12,
  '0.customer.addresses.0.foreignPostalCode': null,
  '0.customer.addresses.0.state': 'FL',
  '0.customer.addresses.0.streetAddress': '819 15th Ave Dr E',
  '0.customer.addresses.0.zipCode': 34211,
  '0.customer.addresses.0.zipPlus': null,
  '0.customer.addresses.1.addressType': 'M',
  '0.customer.addresses.1.city': 'BRADENTON',
  '0.customer.addresses.1.countryCode': 'US',
  '0.customer.addresses.1.countyCode': 12,
  '0.customer.addresses.1.foreignPostalCode': null,
  '0.customer.addresses.1.state': 'FL',
  '0.customer.addresses.1.streetAddress': 'PO BOX 124',
  '0.customer.addresses.1.zipCode': 34201,
  '0.customer.addresses.1.zipPlus': '0124',
  '0.customer.companyName': null,
  '0.customer.customerNumber': 932874,
  '0.customer.customerStopFlag': false,
  '0.customer.customerType': 'I',
  '0.customer.dateOfBirth': '1936-08-05T00:00:00',
  '0.customer.dlExpirationDate': '2022-08-05T00:00:00',
  '0.customer.dlRenewalEligibilityFlag': true,
  '0.customer.driverLicenseNumber': 'B360722339284',
  '0.customer.emailAddress': null,
  '0.customer.feidNumber': null,
  '0.customer.firstName': 'David',
  '0.customer.lastName': 'Brierton',
  '0.customer.middleName': 'Hugh',
  '0.customer.militaryExemptionFlag': null,
  '0.customer.nameSuffix': null,
  '0.customer.sex': 'M' }

Answer №6

function convertObjToCsv(obj, options) {
        if (typeof obj !== 'object') return null;
        options = options || {};
        var scopechar = options.scopechar || '.';
        var delimiter = options.delimiter || ',';
        if (!Array.isArray(obj)) obj = [obj];
        var current, name, rowNumber, key, queue, values = [], rows = [], headers = {}, headersArr = [];
        for (rowNumber = 0; rowNumber < obj.length; rowNumber++) {
            queue = [obj[rowNumber], ''];
            rows[rowNumber] = {};
            while (queue.length > 0) {
                name = queue.pop();
                current = queue.pop();
                if (current !== null && typeof current === 'object') {
                    for (key in current) {
                        if (current.hasOwnProperty(key)) {
                            queue.push(current[key]);
                            queue.push(name + (name ? scopechar : '') + key);
                        }
                    }
                } else {
                    if (headers[name] === undefined) headers[name] = true;
                    rows[rowNumber][name] = current;
                }
            }
            values[rowNumber] = [];
        }
        // create csv text
        for (key in headers) {
            if (headers.hasOwnProperty(key)) {
                headersArr.push(key);
                for (rowNumber = 0; rowNumber < obj.length; rowNumber++) {
                    values[rowNumber].push(rows[rowNumber][key] === undefined
                                        ? ''
                                        : rows[rowNumber][key]);
                }
            }
        }
        for (rowNumber = 0; rowNumber < obj.length; rowNumber++) {
            values[rowNumber] = values[rowNumber].join(delimiter);

        }

          return '"' + headersArr.join('"' + delimiter + '"') + '"\n' + values.join('\n');
    }

If you have a JSON object similar to the following:

{
  "_id": "template:user",
  "_rev": "11-d319c4ac632171d6f01c40fdef3164a5",
  "p": "user",
  "first_name": "first_name_000",
  "last_name": "last_name_000",
  "favorite_list": {
    "field": "value_000"
  },
  "list_kmorganisation": [
    {
      "siren": "siren_000",
      "partition": "partition_000",
      "id_role": "id_role_000",
      "is_default": "is_default_000",
      "is_managed": "is_managed_000",
      "is_banned": "is_managed_000",
      "ban_reason": "ban_reason_000",
      "ban_date": "ban_date_000",
      "last_connection": "last_connection_000"
    }
  ],
  "login": {
    "mail": "mail_000",
    "passwd": "passwd_000",
    "salt": "salt_000",
    "status": "status_000",
    "access": [
      {
        "log_date": "log_date_000",
        "os": "os_000",
        "version": "version_000",
        "ip_addr": "ip_addr_000",
        "screen": "screen_000"
      }
    ]
  }
}

The flattened output will be as follows: "login__access__0__screen", "login__access__0__ip_addr", "login__access__0__version", "login__access__0__os", "login__access__0__log_date", "login__status", "login__salt", "login__passwd", "login__mail", "last_name","first_name", "list_kmorganisation__1__last_connection", "list_kmorganisation__1__ban_date", ...truncated for brevity... "p", "_rev", "_id" (All in one line with corresponding values on the next line)

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Execute a function using a click event within a statement

Is it possible to trigger a function with parameters based on the result of a statement? For example, can we achieve something like this: (click)="datavalue.elementDataCollection.length > 1 ? AddNewDialog (datavalue,datavalue.COCLabel,mainindex,i) : r ...

Encountering an issue when utilizing the Json.NET schema to interpret JSON Schema, where an error

I'm currently exploring JSON schema validation and I decided to start with the example provided on the JSON.NET Help page. However, when I tried to run the code, I encountered an exception. System.MethodAccessException: Attempt by method 'Newton ...

What is the best way to open the index.html file in electron?

I'm currently developing a cross-platform app using electron and Angular. As of now, the code I have for loading my index.html file looks like this: exports.window = function window() { this.createWindow = (theBrowserWindow) => { // Create ...

Steps to make pop-up iframe function on the same page within a react nextjs application

My vanilla Html app has a pop-up feature that functions perfectly. When the button is clicked, the pop-up opens and everything works as expected. However, I am encountering an issue when trying to implement this same functionality in my React, NextJS app. ...

When IntelliJ starts Spring boot, resources folder assets are not served

I'm following a tutorial similar to this one. The setup involves a pom file that manages two modules, the frontend module and the backend module. Tools being used: IDE: Intellij, spring-boot, Vue.js I initialized the frontent module using vue init w ...

Using async/await in React to retrieve data after a form submission

I am currently facing an issue with displaying data fetched from an API on the screen. My goal is to retrieve data when a user types something in a form and clicks the submit button. The error message I am encountering is: TypeError: Cannot read propert ...

Convert inline javascript into an external function and update the reference to `this`

I'm currently in the process of converting some inline JavaScript code triggered by a button's onclick event to a regular JavaScript function. In my previous implementation, I successfully used the "this" reference to remove a table column. Howe ...

The issue of AFrame content failing to display on Google Chrome when used with hyperHTML

There seems to be an issue with A-Frame content not rendering on Chrome, despite working fine on FireFox and Safari. According to the demonstration on CodePen here, const { hyper, wire } = hyperHTML; class Box extends hyper.Component { render() { retu ...

What is the best way to include an array in an object while only retaining a single column for each element in the array?

I am working with an array named answers on my webpage, which has the following structure: answers[{x: 1, r: true;},{x: 2,r: false;},{x: 3, r: true;}] I believe I have defined this correctly. The answers array consists of a variable number of rows (in th ...

To calculate the sum of input field rows that are filled in upon button click using predetermined values (HTML, CSS, JavaScript)

Greetings for exploring this content. I have been creating a survey that involves rows of buttons which, when clicked, populate input fields with ratings ranging from 5 to -5. The current code fills in one of two input fields located on opposite sides of ...

Error: React/Express - The renderToString() function encountered an unexpected token '<'

As I work on implementing server-side rendering for my React/Express application, I have hit a snag due to a syntax error related to the use of the react-dom/server renderToString() method. In my approach, I am following a tutorial mentioned here - The sn ...

Rails Ajax form submission not working

I recently set up a basic Google form on my website and used this website to extract the HTML code for display. However, upon hitting submit, nothing happens - the page seems to be frozen. I'm attempting to redirect the form submission to another page ...

What is the best way to send information back to an AJAX script that has made a

Can someone explain to me how the response section of an AJAX call is processed and formatted using plain, native JavaScript (no jQuery or other frameworks)? I have searched extensively but have not found a clear answer. I am looking for an example that ...

Sending a JSON payload from Angular to C# can result in a value of 0 or null being received

As a beginner working on my Angular&C# project, I encountered an issue. Despite sending a JSON from Angular to C# (POST) with values, the C# side is not receiving anything (value 0 for int and null for string). I can't figure out what I'm doi ...

Can you explain the relationship between HTML 5 Canvas coordinates and browser pixels?

When trying to use HTML 5 canvas as a drawing board in my application, I encountered an issue. The drawings appear at an offset from the mouse pointer. My method involves using the Jquery .offset function to determine the event's position on the canv ...

Javascript regular expression fails to find a match

Is there a way to match all strings except for the ones containing '1AB'? I attempted it but it returned no matches. var text = "match1ABmatch match2ABmatch match3ABmatch"; var matches = text.match(/match(?!1AB)match/g); console.log(matches[0]+" ...

Transform CSV containing hierarchical headers into JSON format

After following a tutorial, I managed to create this code: import csv, json csvFilePath = "convertcsv.csv" jsonFilePath = "newResult.json" # Reading the CSV file and storing the data in a dictionary... data = {} with open(csvFilePath ...

Utilizing React Typescript Discriminating Unions to choose between two different types based solely on props

In my project, I have a component that consists of different types: type Base = { color: string } type Button = { to: string } & Base type Link = { link: string linkNewTab: boolean } & Base type ComponentProps = Button | Link e ...

Iterating over an object in a React component

i'm trying to generate an object using iteration like this: opts = [{label:1, value:1}, {label:4, value:4}] the values for this object are coming from an array portArray = [1,4] I'm attempting to do const portArray = [1,4]; return { ...

Avoid production build warnings in Vue.js without the need to build the code

Experimenting with vuejs, I decided to use it for a simple page even though I could have achieved the same without any framework. Now, my project is nearly production ready. The only issue is that it's just a single js and html file, but it shows thi ...