Updating json field in objectionjs throws Unknown column 'lng' in 'field list

Question

I have a nestjs app where I am using objectionjs and knex to communicate with my mysql db. The address field is a json like:

data = {
   address: { lat: 12, lng: 20 }
}

Whenever I run an update using:

this.personRepository
      .query()
      .update(data)
      .where('id', person.id)
      .execute()

I keep getting this error- Unknown column 'lng' in 'field list'

Full error is something like:

update `persons` set `address` = {"lat":12,"lng":20}, `updatedAt` = '2023-02-28 23:27:39.476' where `id` = X'1234567'

Please help me point out what I'm doing wrong.

you need to [stringify](https://dev.mysql.com/doc/refman/5.7/en/json.html) the variable `data` — Tobok Sitanggang, Mar 01 '23 at 00:23
@TobokSitanggang I thought about this, but since I'm using typescript and the model is typed to json on different parts of the code, stringify just sends me down a rabbit hole that feels very hacky to fix by changing the type in every place where this field appears. — Willower, Mar 01 '23 at 00:27
i got you, i suggest to use type any only for this attribute if there are too much needs to be updated. — Tobok Sitanggang, Mar 01 '23 at 00:56

score 1 · Accepted Answer · answered Mar 01 '23 at 01:34

The JSON fields should be automatically stringified by objection by defining the jsonSchema address property as an object.

class Person extends Model {
  static get jsonSchema() {
    return {
      type: 'object',
      properties: {
        address: { 
          type: 'object',
          properties: {
            lat: { type: 'number' },
            lng: { type: 'number' },
          },
        },
      },
    }
  }
}

Or if you don't have a schema by setting the jsonAttributes property on the Model.

class Person extends Model {
  static get jsonAttributes() {
    return ['address'];
  }
}

Updating json field in objectionjs throws Unknown column 'lng' in 'field list

1 Answers1