How to insert array of objects into Postgres jsonb column using javascript, knex, objection?

Question

I have been trying to insert and array of objects into this jsonb column, but I guess the following error message:

insert into ... (...) values (...) on conflict ("source_id", "source", "type") do update set ... = ... returning "..." - invalid input syntax for type json

The error message is invalid input syntax for type json.

If I try to add an object, it works, but an array of the same object it does not work.

As I am using objection together with knex, I wonder if I can solve it by setting my static jsonSchema() properly.

  static get jsonSchema() {
    return {
      type: "object",
      required: ["myJsonbColumn"],
      properties: {
        myJsonbColumn: {
          type: "object",
        },
      },
    };
  }

Looks like your insert query does something wrong. This issue doesn't have enough information to be able to help. — Mikael Lepistö, Jul 28 '21 at 16:07

ofundefined · Answer 1 · 2021-07-29T14:01:55.657

Found it. This is how I had to define my jsonSchema().

I was not sure the properties field could accept objects.

  static get jsonSchema() {
    return {
      type: "object",
      required: ["myJsonbColumn"],
      properties: {
        myJsonbColumn: {
          type: "array",
          items: { type: "object" },
        },
      },
    };
  }

So for myJsonbColumn (fake column name) I was able to add this object to define its type as array and its their inner array items type as object.

{
  type: "array",
  items: { 
    type: "object" 
  }
}

Which makes sense because I was really trying to save an array of objects in this jsonb column.

How to insert array of objects into Postgres jsonb column using javascript, knex, objection?

1 Answers1