Combining LET statements

SQL++
1

I have a document that has teams split into home and away, along with the home score and away score. I want to get an array of all the winning teams. I can individually get the away winners or the home winners but when I try to combine the LETs I get and error.

The data is:

INSERT INTO default VALUES( “games”, {“week1”: {“games”: [{“gameid”: 1,“away”: “fleet”,“home”: “rangers”,“awayScore”: 1,“homeScore”: 3,“gameComplete”: true},{“gameid”: 2,“away”: “flyers”,“home”: “lions”,“awayScore”: 4,“homeScore”: 1,“gameComplete”: true},{“gameid”: 3,
“away”: “deers”,“home”: “pelicans”,“awayScore”: 7,“homeScore”: 2,“gameComplete”: true},{“gameid”: 4,“away”: “badBoys”,“home”: “killers”,“awayScore”: 2,“homeScore”: 3,“gameComplete”: true},{“gameid”: 5,“away”: “saints”,“home”: “sinners”,“awayScore”: 4,“homeScore”: 0,“gameComplete”: true}]}});

If I run these two queries I get the proper results back:
SELECT RAW teamsAway
LET teamsAway = (SELECT RAW games.away
FROM default AS g USE KEYS "games"
UNNEST g.week1.games AS games
WHERE games.gameComplete = true AND games.awayScore > games.homeScore)

SELECT RAW teamsHome
LET teamsHome = (SELECT RAW games2.away
FROM default AS g2 USE KEYS "games"
UNNEST g2.week1.games AS games2
WHERE games2.gameComplete = true AND games2.awayScore > games2.homeScore)

But if I try to combine them I get an error:
SELECT RAW teamsAway
LET teamsAway = (SELECT RAW games.away
FROM default AS g USE KEYS "games"
UNNEST g.week1.games AS games
WHERE games.gameComplete = true AND games.awayScore > games.homeScore)

LET teamsHome = (SELECT RAW games2.away
FROM default AS g2 USE KEYS "games"
UNNEST g2.week1.games AS games2
WHERE games2.gameComplete = true AND games2.awayScore > games2.homeScore)

[
{
“code”: 3000,
“msg”: “syntax error - at LET”,
“query_from_user”: “SELECT RAW teamsAway\n LET teamsAway = (SELECT RAW games.away\n FROM default AS g USE KEYS “games”\n UNNEST g.week1.games AS games\n WHERE games.gameComplete = true AND games.awayScore > games.homeScore)\n\t\n LET teamsHome = (SELECT RAW games2.away\n FROM default AS g2 USE KEYS “games”\n UNNEST g2.week1.games AS games2\n WHERE games2.gameComplete = true AND games2.awayScore > games2.homeScore)”
}
]

2

LET keyword allowed once, multiple lets needs to separated by comma like below
imagehttps://github.com/couchbase/query/blob/master/docs/diagram/let-clause.png?raw=true

SELECT teamsAway, teamsHome
LET teamsAway = (SELECT RAW games.away
                               FROM default AS g USE KEYS "games"
                              UNNEST g.week1.games AS games
                              WHERE games.gameComplete = true AND games.awayScore > games.homeScore),
         teamsHome = (SELECT RAW games2.away
                              FROM default AS g2 USE KEYS "games"
                              UNNEST g2.week1.games AS games2
                              WHERE games2.gameComplete = true AND games2.awayScore > games2.homeScore);

If you need to combine results into single array ARRAY_CONCAT(teamsAway, teamsHome)

3

Thanks, I missed that on the LET statement

4

Can the results of one LET be used in another LET query.
In the above case, can teamsAway be used within the query for teamsHome?

5

Chaining of LET is not allowed MB-19772. But you can repeat whole query