PostgreSQL JSON Concatenation Operator

jsoncat.md

Postgres lacks convenient operators for modifying JSON or JSONB values. A common operation one might want to do is to change one property in a JSON column across multiple rows, like:

UPDATE example SET json_col = json_col || '{ "prop": true }'::jsonb WHERE <conditions>;

The || operator is the natural choice for this because it is also used for array and jstore concatenation in Postgres.

This short PLV8 function adds the JSON concatenation operator. It is significantly more powerful than the array and hstore counterparts, and are capable of:

• Adding or changing properties at any depth in deeply nested JSON values
• Deleting a property by setting it to null. This has the side effect that null values cannot be stored.
• Replacing an entire subtree by adding a property "_": null to the second operand.
• Replacing an array with a new one
• Appending values to an existing array, by adding a null at the beginning of the array in the second operand.
• Prepending values to an existing array, by adding a null at the end of the array in the second operand.
• Treating an array as a set and adding unique values only, by adding nulls at the beginning and end in the second operand.

Here's the function:

CREATE FUNCTION json_cat (a jsonb, b jsonb) RETURNS jsonb AS $$
	a = JSON.parse(a); b = JSON.parse(b);
    
	var i, j, el = { _: a }, stacka = [el], stackb = [{ _: b }];
	// Wrapping inputs because ONLY objects may be pushed into the stacks
	
	do {
		a = stacka.pop(); b = stackb.pop();
		
		for (i in b) {
			if (b[i] === null) { //             null in object: deletion marker
				delete a[i];
			} else if (b[i] instanceof Array) { //              array operation
				if (a[i] instanceof Array) { //               on existing array
					if (b[i][0] === null && b[i][b[i].length-1] === null) {
						//                             merge without duplicates
						for (j = 1; j < b[i].length - 1; j++) {
							if (a[i].indexOf(b[i][j]) < 0) a[i].push(b[i][j]);
						}
					} else if (b[i][b[i].length-1] === null) { //       prepend
						a[i] = b[i].slice(0, -1).concat(a[i]);
					} else if (b[i][0] === null) { //                    append
						a[i] = a[i].concat(b[i].slice(1));
					} else { //                                         replace
						a[i] = b[i];
					}
				} else { //                         operation creates new array
					if (b[i][0] === null) b[i].shift();
					if (b[i][b[i].length-1] === null) b[i].pop();
					a[i] = b[i];
				}
			} else if (typeof b[i] === "object" && typeof a[i] === "object") {
				if (b._ === null || a[i] instanceof Array) { //  replace object
					delete b._;
					a[i] = b[i];
				} else { //                                       merge objects
					stacka.push(a[i]);
					stackb.push(b[i]);
				}
			} else { //                                     primitives: replace
				a[i] = b[i];
			}
		}
	} while (stacka.length);
	
    return JSON.stringify(el._);
$$ LANGUAGE plv8 IMMUTABLE;

CREATE OPERATOR || ( PROCEDURE = json_cat, LEFTARG = jsonb, RIGHTARG = jsonb );