Saving an array of objects/strings throws an error in postgresql

[ad-on-is]

I already tried raising an issue, but it's been abandoned. So, I'm trying it here again. Seems like, with adonisJS it's not possible to save an array into a JSONB-field in PostgreSQL.

adonisjs/lucid#884

[aayush123]

I faced a similar issue in Mysql when trying to handle JSON columns. Here's what I did to write once and forget about it:

Model:

export default class MyModel extends BaseModel {
  public static latestFirst = scope((query) => {
    query.orderBy('id', 'desc');
  });

  @column({ isPrimary: true })
  public id: number;

  @column()
  public jsonColumn: string;

  @computed()
  public get parsedJsonColumn() {
    const parsedJsonColumn: TransformedEntities.ParsedJsonColumn[] = JSON.parse(this.jsonColumn);
    return parsedJsonColumn;
  }

  @beforeSave()
  public static async handleJSONBeforeSave(model: MyModel) {
    if (typeof model.jsonColumn === 'object') {
      model.jsonColumn = JSON.stringify(model.jsonColumn);
    }
  }
}

This way, you can directly pass a strigified JSON or an object or array to your column and this hook will handle the rest.
You can later access the parsed JSON directly with the computed column

[McSneaky]

You can also use prepare and consume on column decorator https://docs.adonisjs.com/reference/orm/decorators#column

// Optionally add types for your column
type MyJsonColumn = {
  foo: string,
  bar: number
}

@column({
  consume: (value) => JSON.parse(value),
  prepare: (value) => JSON.stringify(value),
})
public jsonColumn: MyJsonColumn

Be sure to make null checks tho, if you allow nullable fields. Since JSON.stringify(null) will give unexpected result