10/23/2015 - 11:20 PM

Using Knex to remove null values from a Postgres JSON column

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
  for (k in {
    if ([k] == null) {
      needsUpdating = true;
  if (!needsUpdating) {
  } 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:}).asCallback(function(err, data){

// 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 ='jsonDataColumn', 'gid').from('dataTable').stream();