Complex N1QL query with UNNEST need other lookup if empty

SQL++
,
7

Oh, this is so cool… :pray: You really are a SQL++ superman! :man_superhero:

I had one slight challenge left (took some testing and verification to find). But if I have a fishing trip without targetspecies and I have more catches of the same type of species then the aggregation counted too many trips and catches (in this sample the double).

This is a simple sample of the data, where I have a fishing trip without targetspecies:

    "catchkeys": [
      "CA99EEDB3096E806C1258AE1004A7AB6",
      "491F860894B10392C1258AE1004A578B",
      "386BF6ED8F7C4415C1258AE1004A4A8B"
    ],
    "key": "4D900788E957FFA1C1258AE1004A4A48",

It has 3 catchkeys and for each of them there is a specieskey (I incorrectly just used species in the question). So for each I have:

{
  "key": "CA99EEDB3096E806C1258AE1004A7AB6",
  "specieskey": "13",
  :
}
:
{
  "key": "491F860894B10392C1258AE1004A578B",
  "specieskey": "13",
  :
}
:
{
  "key": "386BF6ED8F7C4415C1258AE1004A4A8B",
  "specieskey": "32",
  :
}

To get the right species keys back when grouping I just had to change the UNNEST to include DISTINCT:

UNNEST (CASE WHEN ARRAY_LENGTH(d.targetspecies) > 0 THEN d.targetspecies ELSE (
        SELECT DISTINCT RAW c.specieskey
        FROM data AS c USE KEYS (ARRAY 'Catch:'|| v FOR v IN d.catchkeys END)) END) specieskey

… and now it works like a charm! I can’t help being impressed at what you can obtain with SQL++ and Couchbase!

Thanks!

I just wanted to update here in case someone else came across the question and dialogue.