0.3.1: Cannot update JSONB array fields, invalid input syntax

[Q16solver]

Issue Description

I have a jsonb string array

And I update it like this

But in 0.3.1 it does not work anymore and gives me this stack trace, which seems to indicate that this is not transforming the parameters into the correct values anymore

query: UPDATE "group" SET "newestMessageSeenUserIds" = $1, "updatedAt" = CURRENT_TIMESTAMP WHERE "id" IN ($2) -- PARAMETERS: [["7d940bc6-da7f-4288-aa83-b4f1d8ca6323"],"c14ccd9e-5c0e-48ee-ab7b-57b224e60af4"]
query failed: UPDATE "group" SET "newestMessageSeenUserIds" = $1, "updatedAt" = CURRENT_TIMESTAMP WHERE "id" IN ($2) -- PARAMETERS: [["7d940bc6-da7f-4288-aa83-b4f1d8ca6323"],"c14ccd9e-5c0e-48ee-ab7b-57b224e60af4"]
error: error: invalid input syntax for type json
    at Parser.parseErrorMessage (/Users/jimmychen/Desktop/Actually_Uni/Programmin/Software/React/Demos/Esposter-backend/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/Users/jimmychen/Desktop/Actually_Uni/Programmin/Software/React/Demos/Esposter-backend/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/Users/jimmychen/Desktop/Actually_Uni/Programmin/Software/React/Demos/Esposter-backend/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/Users/jimmychen/Desktop/Actually_Uni/Programmin/Software/React/Demos/Esposter-backend/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:390:28)
    at Socket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:199:23) {
  length: 191,
  severity: 'ERROR',
  code: '22P02',
  detail: 'Expected ":", but found "}".',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'JSON data, line 1: {"7d940bc6-da7f-4288-aa83-b4f1d8ca6323"}',
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'jsonfuncs.c',
  line: '621',
  routine: 'json_ereport_error'
}

Expected Behavior

String array should be appended with newest value in database

Actual Behavior

Raises input syntax error

My Environment

Dependency	Version
Operating System
Node.js version	x.y.zzz
Typescript version	x.y.zzz
TypeORM version	0.3.1

Additional Context

Relevant Database Driver(s)

DB Type	Reproducible
aurora-mysql	no
aurora-postgres	no
better-sqlite3	no
cockroachdb	no
cordova	no
expo	no
mongodb	no
mysql	no
nativescript	no
oracle	no
postgres	yes
react-native	no
sap	no
sqlite	no
sqlite-abstract	no
sqljs	no
sqlserver	no

Are you willing to resolve this issue by submitting a Pull Request?

• ✖️ Yes, I have the time, and I know how to start.
• ✖️ Yes, I have the time, but I don't know how to start. I would need guidance.
• ✅ No, I don’t have the time, but I can support (using donations) development. 5$
• ✖️ No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

[pleerock]

you must add array: true for array jsonb support as of #8766

[Q16solver]

I see.. it seems like I also need to update the default value now, instead of string, to just the normal empty array, the default value in postgres does seem to put it as an object though

query: ALTER TABLE "group" DROP COLUMN "newestMessageSeenUserIds"
query: ALTER TABLE "group" ADD "newestMessageSeenUserIds" jsonb array NOT NULL DEFAULT '{}'

It still does give me the exact same update statement though and invalid input syntax,

query: UPDATE "group" SET "newestMessageSeenUserIds" = $1, "updatedAt" = CURRENT_TIMESTAMP WHERE "id" IN ($2) -- PARAMETERS: [["a31c0021-c851-480a-ba9c-a8ada4d31d70"],"c14ccd9e-5c0e-48ee-ab7b-57b224e60af4"]
query failed: UPDATE "group" SET "newestMessageSeenUserIds" = $1, "updatedAt" = CURRENT_TIMESTAMP WHERE "id" IN ($2) -- PARAMETERS: [["a31c0021-c851-480a-ba9c-a8ada4d31d70"],"c14ccd9e-5c0e-48ee-ab7b-57b224e60af4"]
error: error: invalid input syntax for type json
    at Parser.parseErrorMessage (/Users/jimmychen/Desktop/Actually_Uni/Programmin/Software/React/Demos/Esposter-backend/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/Users/jimmychen/Desktop/Actually_Uni/Programmin/Software/React/Demos/Esposter-backend/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/Users/jimmychen/Desktop/Actually_Uni/Programmin/Software/React/Demos/Esposter-backend/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/Users/jimmychen/Desktop/Actually_Uni/Programmin/Software/React/Demos/Esposter-backend/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:390:28)
    at Socket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:199:23) {
  length: 162,
  severity: 'ERROR',
  code: '22P02',
  detail: 'Token "a31c0021" is invalid.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'JSON data, line 1: a31c0021...',
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'jsonfuncs.c',
  line: '621',
  routine: 'json_ereport_error'
}

Do we have a new syntax for updating jsonb array values? Typescript does not complain to me if I do this even though it doesn't work

GroupRepository.update(group.id, { newestMessageSeenUserIds: [...group.newestMessageSeenUserIds, userId] })

[pleerock]

I'll release a fix in 0.3.2 very soon.

[Q16solver]

@pleerock Sweet, confirmed fix on 0.3.2, changed back to default: '[]' and removed array: true as that does not seem to be required, also donated, thanks for the fix 👍

[rocketjsquirrel]

@pleerock , Unfortunately, this still does not work for me.

My field is mapped as:

@Column({ nullable: true, type: 'jsonb', array: true })
dataOptions?: any[];

I get the error: QueryFailedError: malformed array literal: "[{"label":"yourLabel","value":"yourValue","type":"yourType"}]" at PostgresQueryRunner.query (/usr/src/app/src/driver/postgres/PostgresQueryRunner.ts:331:19)

I am using

"typeorm": "^0.3.20"
"@nestjs/typeorm": "^10.0.2",