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?

Henri Kellock · Accepted Answer · 2020-04-28 19:16:57Z

19

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

edited Apr 28, 2020 at 19:16

answered Apr 28, 2020 at 19:06

Henri Kellock

2302 silver badges6 bronze badges

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

1 Comment

Nihal Chandwani Over a year ago

how to use comparison operators? like this . where('user.address ::jsonb @> :address', { address: { state: LessThan (query.pincode) } })

user2263572 · Accepted Answer · 2018-10-15 00:54:00Z

16

Got it working.

Correct syntax:

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

answered Oct 15, 2018 at 0:54

user2263572

5,6165 gold badges40 silver badges61 bronze badges

1 Comment

user2263572 Over a year ago

I should add to be aware of possible SQL injection when using the above syntax. Properly sanitizing any incoming queries is a must.

Igor Nikiforov · Accepted Answer · 2023-12-12 16:37:47Z

15

More modern approach would be to use JsonContains.

import { JsonContains } from 'typeorm'

repo.find({
    where: { address: JsonContains({ state: 'florida' }) },
});

answered Dec 12, 2023 at 16:37

Igor Nikiforov

9172 gold badges12 silver badges24 bronze badges

1 Comment

Ammar Oker Over a year ago

this should be the accepted answer, thanks!

Jose Luis Cueva Tacuri · Accepted Answer · 2021-09-08 16:27:22Z

9

It works for me:

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

answered Sep 8, 2021 at 16:27

Jose Luis Cueva Tacuri

911 silver badge2 bronze badges

Comments

dragansr · Accepted Answer · 2020-10-08 10:54:40Z

2

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

edited Oct 8, 2020 at 10:54

dragansr

4906 silver badges10 bronze badges

answered Jun 16, 2020 at 13:02

Ivan Proskuryakov

1,7422 gold badges23 silver badges32 bronze badges

Comments

Guilherme Pereira Maciel · Accepted Answer · 2021-09-15 15:18:51Z

1

try this:

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

answered Sep 15, 2021 at 15:18

Guilherme Pereira Maciel

111 bronze badge

Comments

nidovic · Accepted Answer · 2023-06-04 10:58:13Z

1

.where("user.address->>'state' = :state", {state: "florida"}).getMany();

answered Jun 4, 2023 at 10:58

nidovic

111 bronze badge

2 Comments

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

En Kratia Over a year ago

@nidovic, this doesn't work for camelcase, for example user.myAddress - gives an error.

Reynaldo Rodriguez L. · Accepted Answer · 2022-07-14 00:16:15Z

0

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

edited Jul 14, 2022 at 0:16

answered Jul 14, 2022 at 0:12

Reynaldo Rodriguez L.

11 bronze badge

1 Comment

Mark Rotteveel Over a year ago

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes.

Ivan Simm · Accepted Answer · 2021-01-26 10:48:31Z

-6

This works for me. I just use TypeORM like function.

.find({
   where: {
      address: Like(`%${add what to search for here}%`),
   },
});

answered Jan 26, 2021 at 10:48

Ivan Simm

414 silver badges9 bronze badges

Collectives™ on Stack Overflow

TypeORM PostgreSQL select where JSON field equals some value

9 Answers 9

1 Comment

1 Comment

1 Comment

Comments

Comments

Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

1 Comment

1 Comment

1 Comment

Comments

Comments

Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related