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ö
– Mikael Lepistö, Commented Jul 28, 2021 at 16:07

ofundefined · Accepted Answer · 2021-07-29 14:01:55Z

2

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.

edited Jul 29, 2021 at 14:01

answered Jul 29, 2021 at 3:44

ofundefined

3,3423 gold badges23 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related