Mongodb sql null/field exists?

by Nov 4, 2015

Using ADS 17.0.2 against a mongodb v 3.0.8. I have a collection where some documents do not include a field. An IS NOT NULL does not filter out those documents from my search (ie select * from mycoll where myfield is not null still returns the documents where that field does not exist). Does the MongoSQL include the equivalent syntax of $exists?

Response

Jenny Nishimura over 7 years ago
MongoSQL has a FIELD_EXISTS function. Here is the link to our documentation:

https://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/MongoDB/page/SQL-Mapping/SQL-Mapping#other_support

The following query returns the documents where “field” exists:

SELECT * FROM collection WHERE FIELD_EXISTS(field)

In ADS version 17, MongoSQL supports an EVAL function that can execute MongoDB JavaScript commands.

EVAL( db.collection.find( { field: { $exists : true } } ) )

Kelly Penhall-Wilson over 7 years ago
Thanks – I somehow missed that.