Typeorm query mysql json data column

Question

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'"

I think you need to access those json objects differently, look at e.g. https://stackoverflow.com/questions/52808304/typeorm-postgresql-select-where-json-field-equals-some-value and https://rahulthinking9.medium.com/how-to-query-jsonb-using-typeorm-and-postgresql-37ff7f1a3ea1 — Matthias, Nov 04 '22 at 10:08

María Gregorio · Answer 1 · 2022-11-04T20:09:38.350

0

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();

edited Nov 04 '22 at 20:09

answered Nov 04 '22 at 20:08

María Gregorio

1
2

Typeorm query mysql json data column

1 Answers1