Returning Invalid syntax for type json on arrays

When your column is type json and you try and insert a JSON array, you get the following error:

{ [error: invalid input syntax for type json]
  length: 154,
  name: 'error',
  severity: 'ERROR',
  code: '22P02',
  detail: 'Expected string or "}", but found "1".',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'JSON data, line 1: {1...',
  file: 'json.c',
  line: '665',
  routine: 'report_parse_error' }

For example, this works in PSQL:

insert into my_table ( data ) values ( '[1,2,3]' );

But this returns an error in node-pg:

client.query('insert into my_table ( data ) values ( $1 )', [ [1,2,3] ], function( error ){
  /* ERROR */
});

We convert javascript arrays into postgres arrays. If you stringify your input array it should work:

client.query('INSERT INTO my_table (data) VALUES ($1)', [ JSON.stringify([1,2,3])], handler);

We don't now the column type in advance so we can't do a auto conversions of the array.

@booo

@booo Yeah, I was thinking that may be the case. Well, in that case, non-issue!

@booo

@booo @brianc This behavior is a bug. Converting data from [{}, {}] to {{}, {}} should not be determined by pg.

The only solution is to use JSON.stringify before running the insert query? What if you're running an ORM? What if you are unaware of this limitation?

Just because pg doesn't know the column type in advance does not mean you should convert to the syntax of column type "array". It might be helpful, but it's also a pretty big assumption and makes it really hard to use an array in a json field, despite it being supported and normal behavior by the database.

The better behavior would be to leave converting data types to the client. Both possibilities would be easy. As is, it's really hard to get the expected behavior using what's available.

see also: #374

@badave

@badave Doesn't your proposed behavior require the same amount of work to insert JSON? You're gonna be stringifying the JSON anyway.

Proposed behavior passing {} or [] into a column without the library being opinionated about converting it into a postgres array type. The larger problem isn't the JSON.stringify, it's the behavior of pg. As a matter of fact, the point should be that I shouldn't have to do a JSON.stringify because the library already handles it with JSON objects without stringifying.

This shouldn't be a mandatory conversion. It's that simple. The database error is "invalid input syntax for type json", an insufficient error message as a user to conclude its caused by the lib transforming the data. It's transforming valid JSON into invalid JSON.

However, if I were using an array type and inserting an array into postgres with the incorrect format, I would understand the invalid input syntax for type array because it isn't JSON format, it requires an array format. At that point I would look for a solution that converted my data into the correct form to insert into the pg.

And I could understand it better if the array column type was any good in postgres, but it's complete crap. I'd prefer to use a json array because it is more recognizable.

Yeah, I see what you're saying. It's a bit funky that node-pg assumes to stringify Objects but not Arrays. I think making it more consistent, or rather, no incoming magic (I like the magic coming out though!) would be for the best. It seems a little arbitrary, especially now that JSON has been added to the data-type mix, that JS arrays are assumed to be PG Arrays, when they could be type json or even hstore.

My preference would be to make no assumptions and leave it up to the library consumer. I'm not sure if that means node-pg should JSON.stringify Arrays and Objects by default, as that would be trading one magic for another. But it certainly does seem like a sane default.

Hi, this issue should be reopened, since in postgres shell is possible to execute:

INSERT INTO public.my_table ("userId", "personId", books) VALUES (1, 1, '[1,2,3]');

Best Regards.

I'm still running into this error even after stringifying the input array. Here's my JSON object:

[
   { serviceId: 2, petId: 5, checked: true },
   { serviceId: 3, petId: null, checked: false }
]

After stringifying:

"[{"serviceId":2,"petId":5,"checked":true},{"serviceId":3,"petId":null,"checked":false}]"

I insert and receive the following error:

{"msec":54.03032702207565,"error":"update \"appointment\" set \"ended_at\" = $1, \"report_card\" = $2, \"staff_notes\" = $3, \"status\" = $4, \"updated_at\" = $5 where \"id\" = $6 - invalid input syntax for type json","data":{"message":"update \"appointment\" set \"ended_at\" = $1, \"report_card\" = $2, \"staff_notes\" = $3, \"status\" = $4, \"updated_at\" = $5 where \"id\" = $6 - invalid input syntax for type json","severity":"ERROR","code":"22P02","condition":"invalid_text_representation","detail":"Expected \":\", but found \",\".","where":"JSON data, line 1: {\"{\\\"serviceId\\\":2,\\\"petId\\\":5,\\\"checked\\\":true}\",...","file":"json.c","line":1198,"routine":"report_parse_error","name":"PgError","isBoom":true,"isServer":true,"data":null,"output":{"statusCode":500,"payload":{"statusCode":500,"error":"Internal Server Error","message":"An internal server error occurred"},"headers":{}}}}

It appears the input is being stringified again. Am I missing something here?

Appreciate any help!

Here's an example of using a json data type & doing a round-trip query from node -> postgres -> and back to node with the json type preserved:

https://github.com/brianc/node-postgres/blob/master/test/integration/client/json-type-parsing-tests.js

Hope that helps!

@brianc

@brianc thanks for this. However, isn't this just testing on a single object and not an array of objects?

@jamesdixon

@jamesdixon using pg for arrays of objects is working fine for me:

require('pg').connect( (error, client, done) => {
  if (error) throw error

  client.query('select $1::json as arr', [JSON.stringify([{foo: 'bar'}])], (error, result) => {
    done()
    if (error) throw error
    console.log(result.rows[0].arr) // => [{ foo: 'bar' }]
    process.exit(0)
  })
})

If you stringify first, you'll need to cast the value to json or jsonb depending on what you're using

@jrf0110

@jrf0110 thanks for this. I've confirmed this does work on insert as well. It appears something is double-stringifying my input.

Appreciate the help, fellas!

💥 Comply with `sharedb` spec

12 years later I'm stuck with this. I use pgtyped to execute my sql. I try stringify() before passing to pgtyped's generated function. No difference. I've tried different combinations of cast and quotes in the sql without luck. I'm not going to persevere. My arrays that were inserting fine when they were buried in an object cannot be the root of a json column value with my current setup, it seems.

open to a PR if you find a way to fix this!

@bbsimonbb

@bbsimonbb Both of those approaches do work, so if you’d like some help, post the usual necessities (minimal reproducible example, debugging details).

Navigation Menu

Repository navigation

Returning Invalid syntax for type json on arrays #442

23 remaining items

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Returning Invalid syntax for type json on arrays #442

Description

Activity

booo commented on Sep 16, 2013

jrf0110 commented on Sep 16, 2013

badave commented on Oct 31, 2013

spollack commented on Oct 31, 2013

jrf0110 commented on Oct 31, 2013

badave commented on Oct 31, 2013

jrf0110 commented on Oct 31, 2013

eriknyk commented on May 12, 2016

jamesdixon commented on Jul 25, 2016

brianc commented on Jul 25, 2016

jamesdixon commented on Jul 25, 2016

jrf0110 commented on Jul 25, 2016

jrf0110 commented on Jul 25, 2016

jamesdixon commented on Jul 25, 2016

23 remaining items

bbsimonbb commented on Feb 13, 2025

brianc commented on Feb 13, 2025

charmander commented on Feb 14, 2025

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions