sequelize, query property on array of objects

Question

I have looked extensively for an answer but could not find a simple solution.

I have a table that contains a column subscriptionHistory

The data can look like so:

[
  {
    "fromDate": "2023-01-24T10:11:57.150Z",
    "userSubscribedToo": "EuuQ13"
  },
  {
    "fromDate": "2022-01-24T10:11:57.150Z",
    "tillDate": "2022-02-24T22:59:59.999Z",
    "userSubscribedToo": "a4ufoAB"
  }
]

I'm trying to find the records of the subscriptions.

In Mongo we do

'subscriptionHistory.$.userSubscribedToo' = 'a4ufoAB'

Nice and easy.

I'm using PostgreSQL and Sequelize,

The following doesn't work.

const totalEarnings = await SubscriptionToken.count({
  where: {
    'subscriptionHistory.$.userSubscribedToo': user.id,
  },
});

Neither do any direct queries

SELECT *
FROM vegiano_dev."subscription-tokens"
WHERE "subscriptionHistory"->>'userSubscribedToo' = 'a4ufoAB'
--WHERE "subscriptionHistory" @> '{"userSubscribedToo": "a4ufoAB"}'

Not sure where to go now :-/

What is the column type of subscriptionHistory?

vicki
– vicki

2023-01-25 10:34:00 +00:00
Commented Jan 25, 2023 at 10:34 — vicki
– vicki, Commented Jan 25, 2023 at 10:34
@vicki JSONB type

Oliver Dixon
– Oliver Dixon

2023-01-25 10:40:00 +00:00
Commented Jan 25, 2023 at 10:40 — Oliver Dixon
– Oliver Dixon, Commented Jan 25, 2023 at 10:40

user330315 · Accepted Answer · 2023-01-25 10:33:48Z

1

You can use a JSON path condition with the @@ (exists) operator:

select *
from vegiano_dev."subscription-tokens"
where "subscriptionHistory" @@ '$[*].userSubscribedToo == "a4ufoAB"'

The @> will work as welll, but because subscriptionHistory is an array, you need to use an array with that operator:

where "subscriptionHistory" @> '[{"userSubscribedToo": "a4ufoAB"}]'

This assumes that subscriptionHistory is defined as jsonb which it should be. If it's not, you need to cast it: "subscriptionHistory"::jsonb

answered Jan 25, 2023 at 10:33

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sequelize, query property on array of objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related