Converting JSON Data to CSV

Incoming MQTT data is often JSON formatted data and in order to extract data values from it.

Storing the data directly as JSON formatted data is also common

However it is also very common to store this data as CSV or a least convert it to CSV for use in legacy systems.

With node-red this is very easy to do with the csv node

Which you will find in the parser group of modes.

The CSV node will concert an incoming JavaScript Object into a CSV string or an incoming CSV string into a JavaScript Object.

The settings for the node are shown below:

JSON to CSV

An incoming JSON string will first need to be converted into a JavaScript object using the JSON node.

This will be the case when reading data from a file.

However when injecting using the inject node or getting JSON data from an MQTT node then the data is already a JavaScript object.

The JSON data is in key:value pairs and you will need to use the key names for the columns.

As an example the following incoming JSON data:

Flat vs Nested JSON data

The csv node only works however for flat JSON data if we have nested data like that below:

function flattenObject(ob) {
    var toReturn = {};

    for (var i in ob) {
        if (!ob.hasOwnProperty(i)) continue;

        if ((typeof ob[i]) == 'object' && ob[i] !== null) {
            var flatObject = flattenObject(ob[i]);
            for (var x in flatObject) {
                if (!flatObject.hasOwnProperty(x)) continue;

                toReturn[i + '.' + x] = flatObject[x];
            }
        } else {
            toReturn[i] = ob[i];
        }
    }
    return toReturn;
}
msg.payload = flattenObject(msg.payload);
return msg;

Note: I didn’t write the code above .I can’t remember if I got it from a forum or chatGPT. I have made it into a subflow to make it easier to use.

If we inject the nested data show previously into the function node we get the following output:

Notice that instead of the keys pressure,temp,battery we have data.pressure,data.temp,data.battery.

So now we just need too edit the CSV node to look like the following:

The video and flow from the video doesn’t include this function node an updated demo flow is below:

Video

Related Tutorials and Resources

• Using the Node-Red Chart Node
• Using the Node Red Status Node
• Node-Red HTTP Request Node for Beginners
• Using the Node-Red Function Node- Beginners Guide