Typeorm jsonb array query

Question

I'm trying to query an array of jsonb.

Sample data in the column

[{"entityId": "1ebi", "entityType": "user", "mode": "editor"}, {"entityId": "F13t", "entityType": "team", "mode": "viewer"}]

Entity definition

@Column({ name: 'details', type: 'jsonb' })
public details: {entityId: string, entityType: string, mode: string}[];

This query doesn't seem to work. I tried this after seeing this StackOverflow post. But my data is an array of JSON.

 this.workbookPermissionRepository
      .createQueryBuilder('wp')
      .select()
      .where('wp.details ::jsonb @> :details', {
        details: {
          entityType: IPermissionEntityTypes.USER,
          entityId: user.slug,
          mode: Not(IPermissionSharingModes.NO_ACCESS),
        },
      })
      .printSql()
      .getMany();

I did not find anything in TypeOrm documentation. Any suggestions are welcome.

score 2 · Answer 1 · answered May 02 '21 at 19:11

The syntax for Postgres when querying on json arrays requires the right side of the query to be wrapped in brackets. In this case try wrapping your query in brackets and JSON stringify-ing it like so:

 this.workbookPermissionRepository
  .createQueryBuilder('wp')
  .select()
  .where('wp.details ::jsonb @> :details', {
    details: JSON.stringify([{
      entityType: IPermissionEntityTypes.USER,
      entityId: user.slug,
      mode: Not(IPermissionSharingModes.NO_ACCESS),
    }])
  })
  .printSql()
  .getMany();

Here is a similar post which may help explain the underlying Postgres query:
Query for array elements inside JSON type

Typeorm jsonb array query

1 Answers1