Step 9 of 18 of the SQL tutorial. A join keeps only the rows that have a partner, so it silently answers a different question than the one you asked. LEFT JOIN keeps every compound and fills the spectrum columns with NULL when there is none. Testing s.id IS NULL then returns exactly the 370 compounds with no IR spectrum of the 541. Mango asks it as {"ir": {"$size": 0}}, because an absent branch is an empty array in the document, not a missing one.
It opens on the query SELECT c.preferred_name, c.formula, s.source_id, s.solvent FROM compounds c LEFT JOIN ir_spectra s ON s.compound_id = c.id WHERE s.id IS NULL ORDER BY c.preferred_name LIMIT 15, which you can run and change in place.
Ask the same question of a chemical dataset in SQL and in Mango and compare the two answers. The database is a SQLite file your browser downloads once and queries locally, so nothing you write ever leaves your machine — which is also why the tool needs JavaScript.