that will provide the following values from this document for a given userUniversityId:
incentiveProgramPreferences.incentiveProgramId
incentivePrograms.userMembershipId
WHERE incentiveProgramPreferences.incentiveProgramId = incentivePrograms.incentiveProgramId AND incentivePrograms.membershipStatus = “active”
I tried this:
SELECT impp.incentiveProgramId,
imp AS membershipId
FROM default:`incentiveMemberships` im
UNNEST im.incentiveProgramPreferences impp
UNNEST im.incentivePrograms imp
LET userMembershipId = (
SELECT userMembershipId
FROM imp
WHERE imp.universityId = impp.userUniversityId )
WHERE META(im).id = TO_STRING(1801234)
AND impp.userUniversityId = 3245
but that did not work. I also tried something like this but can figure out how to get it to work:
SELECT impp.incentiveProgramId,
(ANY v IN imp SATISFIES v.incentiveProgramId = impp.incentiveProgramId END).userMembershipId AS x
FROM default:`incentiveMemberships` im
UNNEST im.incentiveProgramPreferences impp
UNNEST im.incentivePrograms imp
WHERE META(im).id = TO_STRING(1801234)
AND impp.userUniversityId = 3245
but I don’t get anything for the userMembershipId.
Can someone point me in the correct direction to get this query working properly please?
SELECT impp.incentiveProgramId,
(SELECT RAW imp.userMembershipId
FROM im.incentivePrograms AS imp
WHERE imp.userUniversityId = impp.userUniversityId
AND imp.membershipStatus = "active"
AND imp.userMembershipId != ""
LIMIT 1)[0] AS membershipId
FROM default:`incentiveMemberships` im USE KEYS "1801234"
UNNEST im.incentiveProgramPreferences impp
WHERE impp.userUniversityId = 3245;
OR
SELECT impp.incentiveProgramId,
(SELECT DISTINCT RAW imp.userMembershipId
FROM im.incentivePrograms AS imp
WHERE imp.userUniversityId = impp.userUniversityId
AND imp.membershipStatus = "active"
AND imp.userMembershipId != "" ) AS membershipIds
FROM default:`incentiveMemberships` im USE KEYS "1801234"
UNNEST im.incentiveProgramPreferences impp
WHERE impp.userUniversityId = 3245;
Neither actually worked (even after correcting the imp.membershipId to imp.userMembershipId). The first only gives the incentiveProgramId as the results, the second gives the incentiveProgramId and an empty array for the membershipId.
better, however an empty string is a valid userMembershipId for a given incentiveProgramId, so if I remove that filter from the query I get both usermembershipId.
SELECT impp.incentiveProgramId,
(SELECT DISTINCT RAW imp.userMembershipId
FROM im.incentivePrograms AS imp
WHERE imp.userUniversityId = impp.userUniversityId
AND imp.membershipStatus = "active") AS membershipIds
FROM default:`incentiveMemberships` im USE KEYS "1801234"
UNNEST im.incentiveProgramPreferences impp
WHERE impp.userUniversityId = 3245;
ok, here’s the corrected query, thanks for your help.
SELECT impp.incentiveProgramId,
(
SELECT RAW imp.userMembershipId
FROM im.incentivePrograms AS imp
WHERE imp.incentiveProgramId = impp.incentiveProgramId
AND imp.userUniversityId = impp.userUniversityId
AND imp.membershipStatus = "active") AS membershipIds
FROM default:`incentiveMemberships` im USE KEYS "1801234"
UNNEST im.incentiveProgramPreferences impp
WHERE impp.userUniversityId = 3245;```