How to compare two fields of same document?

Hi, I have a documents with fields like “oldQty” and “newQty”. These are optional fields. Now I want to fetch the documents where “oldQty” is not equal to “newQty”(If any field having value should also come in result i.e one field having value another is missing). Here I tried with below query, but getting documents only these two fields are not missing.

select * FROM DataBase WHERE type = ‘inventory’ AND (oldQty is not missing or newQty is not missing) AND (oldQty != newQty)

Can you please suggest query to get result.


I think from your description either both fields must exist and not be equal or only one of the fields should exist; if both are missing the documents shouldn’t be returned.

I would approach it with something like:

AND (oldQty != newQty
    OR (oldQty is missing AND newQty is not missing)
    OR (newQty is missing AND oldQty is not missing)


CREATE INDEX ix1 ON  DataBase (type) 
WHERE type = "inventory" AND IFMISSINGORNULL(oldQty, "xyz") != IFMISSINGORNULL(newQty, "xyz");

FROM DataBase d
WHERE type = "inventory" AND IFMISSINGORNULL(oldQty, "xyz") != IFMISSINGORNULL(newQty, "xyz");

Choose “xyz” not present in the document. It can be any JSON value.

Thank you @dh ,This query working