1

I have data stored in a Postgres 11 table, one of the columns has a json object as it follows:

id data client_id
1 JSON BR_123 
{
   "shed": false,
   "transactions": [
      {
         "id": 1,
         "value": 5000,
         "depositDate": "2021-09-20T10:29:05.000Z",
         "expirationDate": "2022-03-29T10:29:05.000Z",
         "requestDate": "2021-09-17T17:04:43.000Z"
      }
   ],
   "applies": false,
   "importValue": null,
   "depositValue": null
}

What I'm trying to do is select all of the entries that have a 'transactions.value' greater or equal than a given amount.

Sequelize:

const clients = await ComercialImports.findAll({
  where: {
    status: 'Running'
  },
  attributes: ['id', 'mode', 'importerId'],
  include: [
    {
      model: ComercialImportsTransactions,
      as: 'importTransaction',
      attributes: ['id', 'data', 'clientId'],
      required: true,
      where: {
        data: {
          transactions: {
            value: {
              [Op.gte]: 5000
            }
          }
        }
      }
    }
  ]
});

Unfortunately, for some reason this query won't work, as it always return 0 results. I can't seem to access any values stored on the transactions entry. All other entries are accessible (e.g. I can query for all data with 'shed' = true)

What am I doing wrong?

brun_exe
  • 17
  • 8
  • In the where condition I see the top ` data` tag in JSON and I don't see it in the data example. Isn't it the issue? – Anatoly Mar 29 '22 at 20:04
  • @Anatoly the `data` is actually the column of the table. This is not the issue since I can retrieve any other values with it. Including the whole structure of the `transactions`, but not a key inside this one – brun_exe Mar 30 '22 at 11:13
  • `trabsaction` is an array that's why simple condition is not working. To filter JSON array elements you need something like this `select value, (value->>'a')::numeric from json_array_elements('[{ "a": 1}, { "a": 2 }]'::json) where (value->>'a')::numeric < 2` – Anatoly Mar 30 '22 at 17:24
  • @Anatoly so there is no way to do this using sequelize finders? Only with query, is that it? – brun_exe Mar 30 '22 at 18:02
  • I'm afraid so. If Postgresql would have an operator for that then Sequelize might support it but unfortunately it's not the case. Though you can look here https://www.postgresql.org/docs/10/functions-json.html yourself maybe I've missed something – Anatoly Mar 30 '22 at 18:28

0 Answers0