Skip to content

Instantly share code, notes, and snippets.

@brock
Last active October 23, 2015 23:29
Show Gist options
  • Save brock/8e73aee0220ac7f5a86d to your computer and use it in GitHub Desktop.
Save brock/8e73aee0220ac7f5a86d to your computer and use it in GitHub Desktop.
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment