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 · Answer 1 · 2020-04-28T19:16:57.343

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 '20 at 19:16

answered Apr 28 '20 at 19:06

Henri Kellock

190
1
5

how to use comparison operators? like this . where('user.address ::jsonb @> :address', { address: { state: LessThan (query.pincode) } }) – Nihal Chandwani Feb 20 '23 at 11:09

score 16 · Answer 2 · answered Oct 15 '18 at 00:54

16

Got it working.

Correct syntax:

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

answered Oct 15 '18 at 00:54

user2263572

5,435
5
35
57

13

I should add to be aware of possible SQL injection when using the above syntax. Properly sanitizing any incoming queries is a must. – user2263572 Mar 02 '19 at 16:54

score 9 · Answer 3 · answered Sep 08 '21 at 16:27

9

It works for me:

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

answered Sep 08 '21 at 16:27

Jose Luis Cueva Tacuri

91
1
2

score 2 · Answer 4 · edited Oct 08 '20 at 10:54

2

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

edited Oct 08 '20 at 10:54

dragansr

417
6
8

answered Jun 16 '20 at 13:02

Ivan Proskuryakov

1,625
2
23
32

score 1 · Answer 5 · answered Sep 15 '21 at 15:18

1

try this:

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

answered Sep 15 '21 at 15:18

Guilherme Pereira Maciel

11
1

score 1 · Answer 6 · answered Jun 04 '23 at 10:58

1

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

answered Jun 04 '23 at 10:58

nidovic

11
1

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](/help/how-to-answer). – Community Jun 05 '23 at 05:33

Reynaldo Rodriguez L. · Answer 7 · 2022-07-14T00:16:15.133

0

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

edited Jul 14 '22 at 00:16

answered Jul 14 '22 at 00:12

Reynaldo Rodriguez L.

1
1

2

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. – Mark Rotteveel Jul 15 '22 at 15:34

score -5 · Answer 8 · answered Jan 26 '21 at 10:48

-5

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

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

answered Jan 26 '21 at 10:48

Ivan Simm

43
4
9

TypeORM PostgreSQL select where JSON field equals some value

8 Answers8

Linked