Some jsonb functions

[parris]

Hello! First off, just wanted to say this is an awesome library!

I see that pg-mem supports some json/jsonb functionality but was missing jsonb_set/insert and to_jsonb methods (among others). Anyone that's trying to perform updates without first querying the data, may need these methods. For unit testing purposes we can take some shortcuts to simulate these functions.

Disclaimer: the following was written quick and dirty at 1am while working on a side project. As I keep iterating on them, I may think about something more official, but often some quick examples are useful for folks!

import { DataType } from 'pg-mem';

export default (db) => {
  db.public.registerFunction({
    name: 'jsonb_set',
    args: [DataType.jsonb, DataType.text, DataType.jsonb, DataType.bool],
    returns: DataType.jsonb,
    implementation: (original, path, newData, inject) => {
      const pathParts = path.replace(/\{|\}|\"/g, '').split(',');
      const result = JSON.parse(JSON.stringify(original));
      let current = result;

      while (pathParts.length) {
        const currentPart = pathParts.shift();
        if (!current[currentPart] && inject) {
          current[currentPart] = {};
        } else if (!current[currentPart] && !inject) {
          return original;
        }

        if (pathParts.length === 0) {
          current[currentPart] = newData;
        } else if (inject) {
          current = current[currentPart];
        }
      }

      return result;
    },
  });
  db.public.registerFunction({
    name: 'jsonb_insert',
    args: [DataType.jsonb, DataType.text, DataType.jsonb],
    returns: DataType.jsonb,
    implementation: (original, path, newData) => {
      const pathParts = path.replace(/\{|\}|\"/g, '').split(',');
      const result = JSON.parse(JSON.stringify(original));
      let current = result;

      while (pathParts.length) {
        const currentPart = pathParts.shift();
        if (pathParts.length === 0) {
          if (current instanceof Array && !isNaN(parseInt(currentPart, 10))) {
            const position = parseInt(currentPart, 10);
            current.splice(position, 0, newData);
          } else if (typeof current === 'object') {
            current[currentPart] = newData;
          }
        } else if (!current[currentPart]) {
          return original;
        }

        current = current[currentPart];
      }

      return result;
    },
  });
  db.public.registerFunction({
    name: 'to_jsonb',
    args: [DataType.text],
    returns: DataType.jsonb,
    implementation: (original) => {
      return original;
    },
  });
};

[oguimbal]

Hi ! Thanks, thats nice to read :)

FYI, there are very few functions already implemented in pg-mem (they all are here, and none are about jsonb).
I dont have a rule telling wether if a function should or should not be embbeded in pg-mem... the only things I figured:

• Implementing them all is NOT a target, as it would bloat the library
• Not implementing the most used one is annoying for the users (especially when the implementation is not straightforward)

The functions you mention obviously fall in the second category !

Regarding your implementation of set/insert, I've not tested it but it seems to be pragmatic.

That said, I'd add several things:

1. The actual signature of jsonb_set is:

jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])

as you can see, the fourth argument is optional, and the second argument is a text[], not a string... the fact that you're passing a string is just because postgres can implicitely cast strings to text[], but you could very well pass something like ARRAY['a', 'b'].

pg-mem can also do that... but unfortunately, I did not expose any way to declare array arguments through the public API yet (that would lock implementation details).
That said, if you'd like to try a pull request, you could declare a text[] argument using internally exposed types. It would look something like:

function jsonb_set(original: any, path: string[], newData: any, inject?: boolean) {
   // path will already be parsed as a string[]
}

// 1rst overload
db.public.registerFunction({
    name: 'jsonb_set',
    args: [DataType.jsonb, Types.text().asArray(), DataType.jsonb, DataType.bool],
    returns: DataType.jsonb,
    implementation: jsonb_set,
  });

// 2nd overload
db.public.registerFunction({
    name: 'jsonb_set',
    args: [DataType.jsonb, Types.text().asArray(), DataType.jsonb],
    returns: DataType.jsonb,
    implementation: jsonb_set,
  });

2. I think a huge potential performance boost could be achieved by only duplicating the parts that has actually changed (instead of JSON.parse (JSON.stringify())). This gain would be especially important when setting a property on huge object graph like version on {"version": 1, "data": something huge}.

That could look like premature optimization, but since those functions can be called in critical parts such as WHERE expressions, I'm worried it could quickly have significant a performance hit on large jsons and/or large tables.