Returning Invalid syntax for type jsonb on arrays

[aLooseGoose]

I think this is related to #442, but this time with jsonb. Can't insert/update a jsonb column directly with the javascript object. If I use JSON.stringify first, it works. Error is:

UTCERROR:  invalid input syntax for type json
UTCDETAIL:  Expected ":", but found "}".

[jgoux]

Same issue here, trying to insert an array of objects in a jsonb column.
If I do a JSON.stringify first it works as expected but it would be better to rely on the library instead of doing it manually.

My JS array :

const coordinates = [{latitude:43.654327, longitude:7.2010627}, {latitude:43.65835, longitude:7.2018366}]

[joskuijpers]

What is your query?

[jgoux]

@joskuijpers Here is a simple one :

client.query('CREATE TABLE IF NOT EXISTS test_jsonb (test jsonb)', [], (err, _) => {

    if (err) return l(err)

    const coordinates = [
      { latitude: 1, longitude: 1 }
    ]

    client.query('INSERT INTO test_jsonb (test) VALUES ($1)', [coordinates], (err, _) => {

      if (err) return l(err)

      return r()

    })

  })

The error :

{ error: invalid input syntax for type json
    at Connection.parseE (/home/jgx/Applications/locadom/server/node_modules/pg/lib/connection.js:554:11)
    at Connection.parseMessage (/home/jgx/Applications/locadom/server/node_modules/pg/lib/connection.js:381:17)
    at Socket.<anonymous> (/home/jgx/Applications/locadom/server/node_modules/pg/lib/connection.js:117:22)

    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:177:18)
    at Socket.Readable.push (_stream_readable.js:135:10)
    at TCP.onread (net.js:542:20)
  name: 'error',
  length: 176,
  severity: 'ERROR',
  code: '22P02',
  detail: 'Expected ":", but found "}".',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'JSON data, line 1: {"{\\"latitude\\":1,\\"longitude\\":1}"}',
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'json.c',
  line: '1198',
  routine: 'report_parse_error' }

If I wrap coordinates into a JSON.stringify the statement works.

We can see according to the error that the array is turned into an object, so the syntax {{...}} is not working.

[joskuijpers]

Could you try using $1::json and $1::jsonb?

[jgoux]

Same error in both cases.

[charmander]

See #1143.