How to compare two fields of same document?

SQL++
1

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.

Thanks

2

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:

SELECT ...
WHERE ...
AND (oldQty != newQty
    OR (oldQty is missing AND newQty is not missing)
    OR (newQty is missing AND oldQty is not missing)
 )

HTH.

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

SELECT d.*
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.

4

Thank you @dh ,This query working