Inserting json array into jsonb column

[aidinrs]

When I try to insert an array of objects to a jsonb column I get the following error.

let bar = [{
    aaa: 1,
    bbb: 2
}, {
    aaa: 1,
    bbb: 2
}]
db.foo.insert({bar: bar})

error: column "bar" is of type jsonb but expression is of type text[] code: '42804' hint: 'You will need to rewrite or cast the expression.'

Massive js builds the following query: insert into foo (bar) values ($1) But the correct query for pg-promise is: insert into foo (bar) values ($1:json)

[aidinrs]

I found in pg-promise's doc that it can be fixed by doing this

let bar = [{
    aaa: 1,
    bbb: 2
}, {
    aaa: 1,
    bbb: 2
}]
db.foo.insert({
    bar: bar,
    rawType: true,
    toPostgres = function () {
        return db.pgp.as.json(this, false)
    }
})

But I think it is a better idea to have some way of providing formatting in queries for pg-promise.

[dmfay]

The easy way around it is to stringify bar. Type management is mostly deferred to the driver so a real fix might be a bit involved.

[vitaly-t]

@AiDirex simpler version of your Custom Type Formatting would be:

db.foo.insert({
    bar,
    rawType: true,
    toPostgres: db.pgp.as.json
})

But that's if you want the whole object. For just the value you would do:

db.foo.insert({
    bar,
    rawType: true,
    toPostgres: a => db.pgp.as.json(a.bar)
})

[vitaly-t]

But I think it is a better idea to have some way of providing formatting in queries for pg-promise.

I think I wrote it somewhere in the past, that it would be nice if Massive.js were to fully rely on the pg-promise query-formatting engine. And everything that needs custom formatting would be handled via Custom Type Formatting.

[robconery]

From what I understand, PostgreSQL does not consider an array to be valid JSONB. I've run into this before and was able to get around it by essentially wrapping the array in an object: {"thing": [...]}. Not ideal, I suppose.

[robconery]

Strike my comment - I was totally wrong about this. In my defense it might have changed since v9.3 but... arrays can be saved just fine sorry!

[dmfay]

The correct way to use pg-promise formatting for this case:

  it('inserts json/jsonb arrays using custom type formatting', function () {
    return db.normal_pk.insert({
      field1: 'nu',
      json_field: {
        data: ['one', 'two', 'three'],
        rawType: true,
        toPostgres: (p) => {
          return db.pgp.as.format('$1::jsonb', [JSON.stringify(p.data)]);
        }
      }
    }).then(res => {
      assert.equal(res.field1, 'nu');
      assert.deepEqual(res.json_field, ['one', 'two', 'three']);
    });
  });

[vitaly-t]

It is more efficient and safer to replace this:

return db.pgp.as.format('$1::jsonb', [JSON.stringify(p.data)]);

with this:

return db.pgp.as.json(p.data) + '::jsonb';