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.