Using Knex to remove null values from a Postgres JSON column
// 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);