TypeORM jsonb array column

Question

I'm working on a node micro-service, orm and db are respectively typeorm and postgresql

I'm trying to create jsonb array column but I'm probably not doing it the correct way.

Notes

I would normally have accomplished this by simply adding a simple extra entity and relation. In this case I have been asked to use the jsonb type in oder to be able to amend the interface without catering for schema changes.
Storing a simple list of indexed ids would be equally good enough for now.
I am not sure I should be using the array: true column option. I made a few attempts to use a plain jsonb object without succeeding ( "{}"::jsonb ).

My aim:

To store an array of objects with an indexed id column, and to be able to add and remove ids. In case this is not possible a flat indexed string array would do.

e.g:

[ {id: 'some-uuid-000'}, {id: 'some-uuid-001'}, ... ]

or:

['some-uuid-000', 'some-uuid-001', 'some-uuid-002']

Code:

My column definition :

@Column({
        type: 'jsonb',
        array: true,
        default: () => 'ARRAY[]::jsonb[]',
        nullable: false,
    })
    public users: Array<{ id: string }> = [];

I manage to fetch the empty array with

const group = await repo.findOneOrFail({ id: groupId });
console.log('>>>>>', group.users);

which outputs:

>>>>> []

when trying to add an item to the array and persist as below

return repo.update(groupId, { users: [...group.users, { id: userId }] });

I get the following output:

2019-12-21 14:40:44.088 UTC [556] ERROR:  malformed array literal: "[{"id":"cc135b8a-b6ed-4cd7-99fc-396228e74509"}]"
2019-12-21 14:40:44.088 UTC [556] DETAIL:  "[" must introduce explicitly-specified array dimensions.
2019-12-21 14:40:44.088 UTC [556] STATEMENT:  UPDATE "group" SET "users" = $2, "created_at" = CURRENT_TIMESTAMP WHERE "id" IN ($1)
(node:5050) UnhandledPromiseRejectionWarning: QueryFailedError: malformed array literal: "[{"id":"cc135b8a-b6ed-4cd7-99fc-396228e74509"}]"

The output error tells me that the configuration must be wrong as postgres seems to be provided with a plain objects array while expecting a different format/notation. I haven't found much details about this sort of scenarios in the docs.

Davide · Accepted Answer · 2019-12-25 18:30:12Z

36

Eventually I found the following solution: the culprit was the array column option which needs to be explicitly set to false as in the example below:

@Column({
        type: 'jsonb',
        array: false,
        default: () => "'[]'",
        nullable: false,
    })
    public users!: Array<{ id: string }>;

When not set typeorm automatically inferred the postgres column type to be jsonb[] (rather than plain jsonb) which doesn't allow performing jsonb_set operations.

edited Dec 25, 2019 at 18:30

answered Dec 21, 2019 at 22:40

Davide

5641 gold badge6 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Avinash Maurya Over a year ago

One will still get error, error: malformed array literal: "[]"

Hussain · Accepted Answer · 2022-04-04 10:01:37Z

-5

@Column('simple-array', { nullable: true })
  toId!: string[];

answered Apr 4, 2022 at 10:01

Hussain

13810 bronze badges

2 Comments

Tyler2P Over a year ago

Your answer could be improved by adding more information on what the code does and how it helps the OP.

onel0p3z Over a year ago

simple-array is not applicable here because the OP is trying to store JSON in Postgres and not just regular strings. simple-array has the caveat that the values cannot contain commas(,).

Collectives™ on Stack Overflow

TypeORM jsonb array column

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related