Escaped JSON object while inserting JSONB[]

[akshar]

Hi Team,

While inserting records with one of the column having JSONB[ ] type, the array JSON items are inserted as escaped string.

Version: 3.4.3

Steps to reproduce:

await sql` INSERT INTO foobar ${sql(record)}`

items is of type JSONB[ ]

const record = {
  status: "INACTIVE",
  mHash: "hashValue",
  items: [
    {
      market: "m1",
      outcome: false,
    },
    {
      market: "m2",
      outcome: true,
    },
  ]

Items are inserted as:

{"{\"market\": \"m1\", \"outcome\": false}","{\"market\": \"m2\", \"outcome\": true}"}

[dagual]

I am also interested in how to write JSON to the database without escaping quotes. Using JSON.stringify(record) also results in escaped quotes in database.

[porsager]

Postgres.js does stringify for you. It's as straight forward as it can be.

If you insert a string that contains json into a json column, then yes, it'll stringify it again. We cannot handle that case without introducing ambiguity.

There is an edge case with regards to simple values (not array or object) #483, but that doesn't seem like the issue here.

[dagual]

I realized what my mistake was. It was necessary to pass json as the value of an object property. The name of the property must match the name of the column in the table.

await sql` INSERT INTO foobar ${sql({record}, 'record')}`

Thanks again so much for your library.