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 :-/
CodePudding user response:
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