Extracting matching array element from within a document not working

SQL++
,
1

Given this sample document with an id of 1801234

{
  "incentiveProgramPreferences": [
    {
      "incentiveProgramId": "1422e858-bae7-48ab-befe-88e9a95ced2d",
      "userUniversityId": 3245
    }
  ],
  "incentivePrograms": [
    {
      "incentiveProgramId": "de890b39-90c3-4ccc-b893-9d65702a1e14",
      "membershipStatus": "active",
      "userMembershipId": "",
      "userUniversityId": 3245
    },
    {
      "incentiveProgramId": "1422e858-bae7-48ab-befe-88e9a95ced2d",
      "membershipStatus": "active",
      "userMembershipId": "1801234",
      "userUniversityId": 3245
    }
  ],
  "userId": 1801234
}

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?

2 
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;
3

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.

4

Updated previous post, Try again

WITH doc AS ( { "incentiveProgramPreferences": [ { "incentiveProgramId": "1422e858-bae7-48ab-befe-88e9a95ced2d", "userUniversityId": 3245 } ], "incentivePrograms": [ { "incentiveProgramId": "de890b39-90c3-4ccc-b893-9d65702a1e14", "membershipStatus": "active", "userMembershipId": "", "userUniversityId": 3245 }, { "incentiveProgramId": "1422e858-bae7-48ab-befe-88e9a95ced2d", "membershipStatus": "active", "userMembershipId": "1801234", "userUniversityId": 3245 } ], "userId": 1801234 } ) 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 doc AS im UNNEST im.incentiveProgramPreferences impp WHERE impp.userUniversityId = 3245;
{
    "requestID": "626376b7-f91d-4c20-b8b2-1b3bfa19c591",
    "signature": {
        "incentiveProgramId": "json",
        "membershipId": "json"
    },
    "results": [
    {
        "incentiveProgramId": "1422e858-bae7-48ab-befe-88e9a95ced2d",
        "membershipId": "1801234"
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "3.605591ms",
        "executionTime": "3.527395ms",
        "resultCount": 1,
        "resultSize": 111,
        "serviceLoad": 2
    }
}
5

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;

[
  {
    "incentiveProgramId": "1422e858-bae7-48ab-befe-88e9a95ced2d",
    "membershipIds": [
      "",
      "1801234"
    ]
  }
]

The sub-query’s where clause seems not to be working.

6

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;```
1 Like