TypeORM PostgreSQL select where JSON field equals some value

Question

Goal: write a select query that returns all rows where state equals "florida".

Entity column:

  @Column({ type: 'json'})
  public address: Address;

Sample column value:

{"city": "miami", "state": "florida"}

Example query (doesn't work):

getManager().getRepository(User)
    .createQueryBuilder('user')
    .select()
    .where('user.address.state =:state', {state: "florida"})

Is this functionality currently supported in typeorm? If so, how would I need to modify my where clause to return the correct rows?

Solution 1

Got it working.

Correct syntax:

.where(`user.address ::jsonb @> \'{"state":"${query.location}"}\'`)

Solution 2

Another possible solution (raw SQL can not be injected with this):

.where('user.address ::jsonb @> :address', {
    address: {
        state: query.location
    }
})

With this, TypeORM will produce an SQL query ending with

WHERE user.address ::jsonb @> $1

and the query will be given e.g.

{ state: 'florida' }

as a query parameter to replace the right value ($1) in the query.

Sources: Mainly the original question + answer (thank you!) + own testing + https://www.postgresql.org/docs/9.4/functions-json.html

Solution 3

    return getRepository(User)
    .createQueryBuilder()
    .where('address @> :address', {
      address: { state: "florida" },
    })
    .getMany();

Solution 4

It works for me:

.getRepository(User)
.createQueryBuilder('user')
.where(`user.address->>'state' = :state`, {state: "florida"})

Solution 5

try this:

   const users = await getManager()
  .getRepository(UserEntity)
  .createQueryBuilder(`user`)
  .where("address->>'state' = :state")
  .setParameters({ state })
  .getMany();

View more solutions