Best way to cast a json array as a json type instead of json[] type using postgres params

[btakita]

const array = [{name: 'foo'}, {name: 'bar'}]
pg.query(`
INSERT INTO tbl (field)
VALUES ($1::json)
`, { params: [array] })

Results with the error:

DatabaseError: cannot cast type json[] to json

What would be the best way to coerce the array to a json type instead of a json[] type?

[erayhanoglu]

Hi, Your query is wrong. You pass an array of json object as an argument but trying to cast to a json. Please try this.

INSERT INTO tbl (field)
VALUES ($1::json[])

By the way, you don't need to cast. I think the following sql code will also work.

INSERT INTO tbl (field) VALUES ($1)

[btakita]

Thank you for the response. The issue is field is type json, not json[]. Casting as json[] results in another error

DatabaseError: column "field" is of type json but expression is of type json[]

I did find a solution...which is to use json_populate_record, but did not figure out how to use postgres params in postgresql-client...

    const sql = `
INSERT INTO portfolio(${param_a_sql})
SELECT ${param_a_sql}
  FROM json_populate_record(NULL::record_type, '${json}')
ON CONFLICT (email, name)
DO UPDATE
    SET (${param_a_sql})=((
        SELECT ${param_a_sql}
            FROM json_populate_record(NULL::record_type, '${json}')
    ))
RETURNING *
    `.trim()

[erayhanoglu]

Sorry for my misunderstanding. The solution is very simple, just use a BindParam instance. By default library requires BindParam for parameters. If you pass any other value, it makes a try to find the correct OID. In the example above you pass an Array value. So it thinks this is an OID "array". Try the code below.

const array = [{name: 'foo'}, {name: 'bar'}]
pg.query(`
INSERT INTO tbl (field)
VALUES ($1::json)
`, { params: [new BindParam(DataTypeOIDs.json, array)] })