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 :-/

>Solution :

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