INNER NEST on empty/undefined fields breaks query

Inner nest with null fields breaks query.

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;

this is what i was looking for

   LEFT NEST `dev` lastMessage
   ON KEYS convo.lastMessage
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;

Thank you for this, it’s much cleaner now.

Quick question, so when I query using SELECT ... FROM, instead of NEST /JOIN it’s much faster I suppose? or it’s all the same under the hood.

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 .