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

score 78 · Answer 1 · edited Jun 28 '21 at 20:08

78

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)

edited Jun 28 '21 at 20:08

abatishchev

98,240
88
296
433

answered Jun 12 '19 at 13:07

A.Rowan

1,460
2
16
20

2

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. – Kun Hu Dec 04 '19 at 17:07
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. – A.Rowan Mar 11 '20 at 19:30

score 26 · Accepted Answer · edited Dec 02 '20 at 18:43

26

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)

edited Dec 02 '20 at 18:43

Koray Tugay

22,894
45
188
319

answered Sep 06 '18 at 15:03

Nick Chapsas

6,872
1
20
29

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? – JDT Sep 06 '18 at 15:08
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. – Nick Chapsas Sep 06 '18 at 15:14
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? – JDT Sep 06 '18 at 15:16
2

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`. – Nick Chapsas Sep 06 '18 at 15:38
Ok - thanks, I will just make sure that "abc" never contains null. – JDT Sep 06 '18 at 15:42
This answer is incorrect. To determine if the field actually exists, you need to do what @ARowan said – Lee Z Jul 13 '21 at 17:41

score 11 · Answer 3 · edited Jul 25 '22 at 16:10

11

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.

edited Jul 25 '22 at 16:10

Klors

2,665
2
25
42

answered Jan 10 '22 at 01:42

Radespy

336
2
11

Check if field exists in CosmosDB JSON with SQL - nodeJS

3 Answers3