Typeorm: Raw query with json columns (Postgres)

Created on 24 Jul 2017 · 3Comments · Source: typeorm/typeorm

Folks, I am trying to build a query like this

``let query =SELECT "incident".* FROM "incident" "incident"
WHERE incidents::jsonb @> '[{"name":$1}]' ORDER BY "incident"."id" ASC`;

const results: any = await repository.query(query, ['Briga']);

I want to add the value of the json as a parameter, but I am getting an error

`error during executing query:error: invalid input syntax for type json
`

The final query I want is this one:

SELECT "incident".*
FROM "incident" "incident" WHERE incidents::jsonb @> '[{"name":"briga"}]'
ORDER BY "incident"."id" ASC
```

I searched in the documentation how to parametrize queries with json fields but could not find anything. Could you help me?

question

Source

brunosiqueira

👍1

Most helpful comment

repository.query calls underlying pg package's query method and uses its parameters syntax. Refer to pg package how to resolve your problem. But in my opinion you should do:
incidents::jsonb @> $1 and set to parameter the whole json.