Using Knex to remove null values from a Postgres JSON column

delete-nulls-from-postgres.js

// Remove rows from a JSON object where the value is null

// use module.export to pass in your postgres connection from knex
var pg = require('../index');
var Promise = require('bluebird');
// not included as a "require", but to use streams you'll need to have installed pg-query-stream
var Writable = require('stream').Writable;

// specify objectMode: true since we'll be passing in a JSON object
var ws = Writable({objectMode: true});
// overwrite the _write method (always required with writable streams)
ws._write = function (chunk, enc, next) {
  // if this row doesn't have any null values, we won't update it in postgres
  var needsUpdating = false;
  // the for loop in javascript and node is syncronous
  // that is important, otherwise you couldn't guarantee that all null values were removed
  // http://stackoverflow.com/a/23717190/2083544
  for (k in chunk.data) {
    if (chunk.data[k] == null) {
      delete chunk.data[k];
      needsUpdating = true;
    }
  }
  if (!needsUpdating) {
    next();
  } else {
    console.log('removing a null value');
    // use Knex's callback feature to only call next after the update is complete
    pg('dataTable').where({gid: chunk.gid}).update({jsonDataColumn: chunk.data}).asCallback(function(err, data){
      next();
    });
  }
};

// this is what actually gets executed
// it starts streaming in all rows in the table, 
// passing them immediately to your writable stream defined above
var stream = pg.select('jsonDataColumn', 'gid').from('dataTable').stream();
stream.pipe(ws);