For example the last inner nest with lastMessage sometimes it’s empty or not defined, but it breaks all query, is there a way to return empty for inner nest that don’t return values.
This fails because lastMessage might be null
SELECT *
FROM `dev` convo
INNER JOIN `dev` owner
ON KEYS convo.owner
INNER NEST `dev` members
ON KEYS convo.members
INNER NEST `dev` lastMessage
ON KEYS convo.lastMessage
WHERE convo._type = "ChatConvo"
LIMIT 1;
but this works because all fields have values and are never undefined.
SELECT *
FROM `dev` convo
INNER JOIN `dev` owner
ON KEYS convo.owner
INNER NEST `dev` members
ON KEYS convo.members
WHERE convo._type = "ChatConvo"
LIMIT 1;
SELECT *
FROM `dev` convo
LET owner = (SELECT o.* FROM `dev` AS o USE KEYS convo.owner),
members = (SELECT m.* FROM `dev` AS m USE KEYS convo.members),
lastMessage = (SELECT l.* FROM `dev` AS l USE KEYS convo.lastMessage)
WHERE convo._type = "ChatConvo"
LIMIT 1;
In this case you have document keys and not using ANSI JOIN/NEST.
Mostly under neath same. But note JOIN can be 1:n and LIMIT 1 can break in the middle .