How would you execute this query with typeorm?
SELECT * from t_integration_app WHERE settings = JSON_OBJECT("modelType", JSON_ARRAY("ACTION"));
I tried something like:
repository.find({
where: {
settings: { modelType: "ACTION" }
},
})
but it fails with this error message
"errorMessage": "Unknown column 'modelType' in 'where clause'"
This worked!
await repository
.createQueryBuilder('t_integration_app')
.where({ ...filter })
.andWhere(
`t_integration_app.settings = JSON_OBJECT("modelType", JSON_ARRAY(:modelType))`,
{ modelType: filterBy.modelType }
)
.getMany();
I also wanted to filter using a JSON Object, but your solution didn't really do the Job. Looking at the raw MySQL query there seems to be a way to query using JSON Columns as where clause mysql json where clause.
From that I was able to get this (adapted to your question).
await repository
.createQueryBuilder('t_integration_app')
.where({ ...filter })
.andWhere(
`t_integration_app.settings ->>"$.modelType" = :modelType`,
{ modelType: filterBy.modelType }
)
.getMany();
This worked for me and should work for even further nested JSON Objects.
