Check if field exists in CosmosDB JSON with SQL - nodeJS

user_8808535 0
Viewed 41362

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

azure-cosmosdb azure sql json
3 Answers

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)
user_65198130

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)

user_75239740

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 include all entries where the FieldName abc doesn't exist.

user_57173700
Related