TypeORM jsonb array column

Issue

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

1. 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.
2. Storing a simple list of indexed ids would be equally good enough for now.
3. 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.

Solution

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.

Answered By - Davide