Insert record in mysql with adonisjs jsonb field

[marcelodelta]

I'm trying to insert a record where one of the fields is of type JSONB

Below is the format I'm trying to build and at the end the error that is being issued.

The field in question is veiculo

orm is trying to access the inside of the json to try to indetify the field.

It should insert the complete object.

I thank the attention. Thanks.

Post

{
  veiculo: {
    placa: 'PLACA',
    motorista: 'NOME',
    contato: 'CONTATO',
    casa: true,
    horaEntrada: '2022-01-20T05:02:22.000Z'
  },
  fornecedor: 'ALIMENTOS',
  lab: 'sim',
}

Model

import { DateTime } from 'luxon'
import { BaseModel, column } from '@ioc:Adonis/Lucid/Orm'

export default class Produto extends BaseModel {
  @column({ isPrimary: true })
  public id: number

  @column()
  public fornecedor: string

  @column()
  public lab: string

  @column()
  public veiculo: Object

  @column.dateTime({ autoCreate: true })
  public createdAt: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  public updatedAt: DateTime

Migration

public async up () {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')
      table.string('fornecedor'
      table.string('lab')
      table.jsonb('veiculo')

      /**
       * Uses timestamptz for PostgreSQL and DATETIME2 for MSSQL
       */
      table.timestamp('created_at', { useTz: true })
      table.timestamp('updated_at', { useTz: true })
    })
  }

Error

Error: ER_BAD_FIELD_ERROR: Unknown column 'placa' in 'field list'

[thetutlage]

Just JSON.stringify the value before writing it to the database.

[marcelodelta]

doing so will it be possible to use query using json in the query?

thank you

[stereobooster]

There is https://github.com/watzon/adonis-jsonable, but I think it doesn't work with latest adonis

[stereobooster]

Something like this maybe

  @afterFetch()
  public static async jsonify(edges: Edge[]) {
    await Promise.all(
      edges.map((edge) => {
        edge.metadata = JSON.parse(edge.metadata)
        return edge
      })
    )
  }

  @beforeSave()
  public static async stringify(edge: Edge) {
    edge.metadata = JSON.stringify(edge.metadata)
  }

[galaczi]

Maybe use a raw query?

https://docs.adonisjs.com/reference/database/raw-query-builder#document

[stereobooster]

Second idea to use custom type parser https://docs.adonisjs.com/cookbooks/using-postgres-custom-type-parsers