Objection.js: jsonb_set - any equivalent method or drop down to raw SQL to perform?

[makeitrein]

Hey all - just sanity checking here - would love to update a postgres json column, but don't see a better way than querying my model, modifying the json object, and resaving.

Has anyone done anything like this w/ Objection and Knex in a clean manner? Thx!

[allxie]

+1

[fl0w]

Unless I'm mistaken, object manipulation functionality was added in postgresql 9.5, and so the original work flow was what you just described - fetch entire object, manipulate in application and save the updated object in full.

If anything about this gets added, I purpose adding some warning regarding compatibility for pg setups < 9.5.

[koskimas]

You have to use raw SQL or save the entire object at the moment. Is @elhigu (our jsonb mastermind) interested in taking this on?

[elhigu]

I could implement this one, but I'm not sure about the API how to support it.

Trival way would be to just add some .updateJsonColumn(...) custom method, other would be to create some kind of wrapper function for JSON column update value which is interpreted as partial update and implemented with jsonb_set...

Any ideas?

[koskimas]

so the second option would look something like this?:

const jref = Person.jsonRef;

Person
  .query()
  .patch(jref('foo.bar.baz'), 100);

Person
  .query()
  .patch({
    normalCol: 'lol',
    [jref('foo.bar.baz')]: 100
  });

I think this would be pretty cool. Isn't a function like jref here something you have considered for all jsonb queries? So that we could deprecate the special whereJson* methods?

[fl0w]

What you described @koskimas looks to be super clean, I love it.

[elhigu]

Actually since http://vincit.github.io/objection.js/#fieldexpression which is used to refer json fields in queries is superset of knex's schema, table / column reference string we could maybe use it directly:

Person
  .query()
  .patch({
    normalCol: 'lol',
    'jsonCol:foo.bar.baz': 100
  });

And use the ref syntax, when reading value from other json field (or any other column) and avoid using knex.raw directly:

Person
  .query()
  .patch({
    normalCol: ref('jsonCol:foo.bar.baz'),
    'jsonCol:foo.bar.baz': ref('normalCol')
  });

This breaks if someone uses : in their column names though... but knex already breaks if . is used so probably its ok... any other drawback on this approach?

[koskimas]

Even better! Can't see any other drawbacks but the possible : in a column name. Maybe we could add a setting that can be used to disable the json column syntax for the normal methods.

[elhigu]

I started working on this + ref() support to reduce need for knex.raw everywhere.

[makeitrein]

Thx @elhigu! Definitely a boon to our company's code base.

[koskimas]

@elhigu I actually started looking into this once, but realized that the whole architecture needs to be somewhat modified. I don't remember all the obstacles, but they were related to the way objection strips all raw expressions, QueryBuilders and other "non-primitives" from the objects in the $setJson method of ModelBase. The new raw and ref wrappers need to be stripped there also, but we need the query builder when we merge them back to the model in $$toJson. There is no way to pass the query builder to that method, and I don't think we shouldn't pass it there so we need to move the stripping stuff somewhere else. I tried moving it to the UpdateOperation and InsertOperation methods, but that doesn't work because of how insertWithRelated.

Just remember to write tests for insertWithRelated as you do this and you'll find the same problems fast. The raw, ref and other wrappers should work in any object (nested or otherwise) with insertWithRelated.

[elhigu]

@koskimas Thanks for the heads up, I'll keep going until I hit the wall. Probably some of the stuff I'm writing will be useful (like bunch of test cases I'm doing for the feature).

[koskimas]

I didn't mean you will hit a wall. I found the problems after couple of days work. I just wanted you to find those obstacles faster.

[elhigu]

implemented in https://github.com/Vincit/objection.js/pull/270 I would consider that insertGraph stuff as a separate issue, update and patch works though! Also with plain insert one usually inserts just complete data of jsonb column at once...

[makeitrein]

You rock! 2017 is off to a good start for my codebase... I'll start playing
around with it, see if any issues or peculiarities crop up

On Wed, Jan 4, 2017 at 1:30 AM Mikael Lepistö [e-mail]
wrote:

implemented in #270 https://github.com/Vincit/objection.js/pull/270

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/Vincit/objection.js/issues/187#issuecomment-270329217,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AB-P7w0mB6rq2pI8Ap1ar5E4RcfQ0q2gks5rO2acgaJpZM4J2bxd
.