Objection.js: Issues updating a field inside a `jsonb` column

[rictorres]

i started reading https://github.com/Vincit/objection.js/issues/187 and ended up finding https://github.com/Vincit/objection.js/pull/270. from what i understood (reading the tests), it's possible to update fields inside a jsonb column.

i've tried something like:

Campaign
  .query()
  .patchAndFetchById(id, { 'data:name': 'testing' })
  .returning('*')

which outputs the following:

-- bindings: [ 'testing', '53248392217756705' ]
update "campaigns"
set "data" = jsonb_set("data", '{name}', to_jsonb($1), true)
where "campaigns"."id" = $2
returning *

and returns this error:

error: could not determine polymorphic type because input has type "unknown"

however, if i cast the value i want to update to text, it works:

update "campaigns"
set "data" = jsonb_set("data", '{name}', to_jsonb('testing'::text), true)
where "campaigns"."id" = '53248392217756705'
returning *

am i missing something? is there a way to do this directly using objection?

[fl0w]

I'm not sure (haven't had the joy to test objection.js json features), but I think you want to wrap your value in a ref and cast if necessary.

[koskimas]

@elhigu ping

[elhigu]

@rictorres Strange that type of $1 is unknown... for now you probably need to use raw to add casting like this:

model = require('objection').Model.bindKnex(require('knex')({client: 'pg'}))
model.query().patchAndFetchById(1, { 
  'data:name': model.raw('?::text', ['testing']) 
}).toString()

update  
  set "data" = jsonb_set("data", '{name}', to_jsonb('testing'::text), true) 
  where "null"."id" = 1

There is issue about having lit() builder which is similar to ref() to be able to pass values with casting easier.

[rictorres]

nice!

i actually wrote it like:

Campaign
    .raw(`UPDATE ${Campaign.tableName}
             SET data = data || '${JSON.stringify(input.data)}'
           WHERE id = ${identifier}
       RETURNING *
    `)

will play a bit more with it tomorrow. thanks a lot!!

[aconran]

I tried to follow the advice of @elhigu using model.raw but my problem was the first argument needed to be run through to_jsonb.

Given a table like

            await knex.schema.createTableIfNotExists('Person', table => {
                table.increments('id').primary();
                table.string('firstName');
                table.string('lastName');
                table.string('suffix');
                table.json('address').defaultTo('{}').notNullable()
                table.timestamps(true, true);
            });

Where the address jsonSchema is:

                address: {
                    type: 'object',
                    properties: {
                        street1: {type: 'string'},
                        street2: {type: 'string'},
                        city: {type: 'string'},
                        state: {type: 'string', minLength: 2, maxLength: 2},
                        zipCode: {type: 'string'}
                    }
                },

If I only want to update a single subkey of the address I tried to do the following:

    Person.query().patchAndFetchById(person.id, {
        'address:street1': Person.raw('?::text', ['123 West Main St'])
    })

This generates the SQL
error: update "Person" set "address" = jsonb_set("address", '{street1}', to_jsonb($1::text), true), "updated_at" = $2 where "Person"."id" = $3 returning * - function jsonb_set(json, unknown, jsonb, boolean) does not exist

Running this in the PG Console I was able to get it working as expected from following this example (https://gist.github.com/faulker/6fa6cc7fec7dc851c92d6b0d984e7b6e).

I changed it to
update "Person" set "address" = jsonb_set(to_jsonb(address), '{street1}', to_jsonb('123 West Main St'::text), true) where "Person"."id" = 1

Any ideas on how can I get objection to generate similar statement without using raw for the entire thing?

[aconran]

For others following along; my issue was using json in the table definition instead of jsonb.

I had switched because to_jsonb doesn't exist in PG 9.4 (it was added in 9.5) and was trying to see if putting json would call to_json instead of to_jsonb

It would be nice if we could easily infer PG type based off of a jsonSchema to eliminate the need for explicit casts.