Check if field exists in CosmosDB JSON with SQL - nodeJS

Question

I am using Azure CosmosDB to store documents (JSON).

I am trying to query all documents that contain the field "abc", and not return the documents that do not have the field "abc". For example, return the first object below and not the second

{
    "abc": "123"
}

{
    "jkl": "098"
}

I am trying to use the following code:

client.queryDocuments(
collectionUrl,
`SELECT r.id, r.authToken.instagram,r.userName FROM root r WHERE r.abc`
)

I assumed the above would check if abc exists similar to if (r.abc) {}

I have tried using WHERE r.abc IS NOT NULL

Thanks in advance

Solution 1

All you need to do is change your query to

SELECT r.id, r.authToken.instagram,r.userName FROM root r WHERE r.abc != null

or

SELECT r.id, r.authToken.instagram,r.userName FROM root r WHERE r.abc <> null

Both operators work (tested on the Data Explorer)

Solution 2

If you want to know if a field exists you should use the IS_DEFINED("FieldName") If you want to know if the field's value has a value the FieldName != null or FieldName <> null (apparently)

I use variations of this in production:

SELECT c.FieldName
FROM c 
WHERE IS_DEFINED(c.FieldName)

Solution 3

Add the NOT operator in the SQL query to negate.

SELECT r.id, r.authToken.instagram,r.userName 
FROM root r 
WHERE NOT IS_DEFINED(r.abc)

to exclude all entries where the FieldName abc doesn't exist.

Comments

[JDT]

does this not check for the string null - thus assuming "abc":"null" does not exist? - Or at least that "abc": null does not exist, rather than that "abc" stricly exists?

[Nick Chapsas]

No this is checking whether the value is null (not string "null", but just null) OR whether the property named abc is part of the json body.

[JDT]

ah ok thanks - is there a way to strictly check if the property name is part of the json? Thus ignore if the value is null too?

[Nick Chapsas]

Nothing that I can find in predefined functions. You would have to write a UDF yourself. Even IS_DEFINED() will return if the value is null.

[JDT]

Ok - thanks, I will just make sure that "abc" never contains null.

[Kun Hu]

IS_DEFINED is particularly useful to find documents that don't have some field populated, when the serialization ignores the null fields. In this case, IS_NULL doesn't work.

[A.Rowan]

I'm writing a query right now to give me a list of records which haven't had an analytic calculated on them. It's great.

[Lee Z]

This answer is incorrect. To determine if the field actually exists, you need to do what @ARowan said