MongoDB extended operator rules erroneously applied to SQL databases and json bodies

NOTE: This is a bug in loopback-datasource-juggler, however the issue template there explicitly requests filing bugs in this project so 🤷

https://github.com/loopbackio/loopback-datasource-juggler/pull/1662 brought in some validation code that only is applicable to MongoDB, and is making certain operation on JSON properties/columns with SQL databases excessively difficult.

It is already impossible to do json equality searches out of the box due to #477, however that issue has a fairly straight forward workaround.

That workaround fails if the JSON object you are searching for contains any properties starting with a $ at the top level however, because they are interpreted as extended operators, even if they cannot possibly actually be such.

For example, pretend #477 was already fixed and you wrote this where expression:

{ where: { propertyName: { eq: { $foo: 'bar' } } } }

The $foo, AFAICT, cannot possibly be an operator because you already specified the eq operator.

Furthermore, if you're not using MongoDB, then this whole thing is moot anyways and these checks are inapplicable.

Steps to reproduce

1. Create an entity type with a property of object type mapped to a SQL field of json type
2. Insert a record that has a key in this property, at the top level, whose name starts with a $
3. Attempt to do a find() call to locate this record using an equality match on that property

Current Behavior

1. #477 gets in your way
2. Even with the workaround for that, Loopback throws a OPERATOR_NOT_ALLOWED_IN_QUERY error and refuses to run your query

Expected Behavior

Loopback should be able to do property = ? queries on JSON fields against SQL databases.

Link to reproduction sandbox

WIP

Additional information

Same environment as #477

Related Issues

• #477

Start with the validation code introduced by loopback-datasource-juggler pull request 1662 and reproduce the issue through a find() call on a PostgreSQL JSON field containing a top-level $ key. Done means SQL JSON equality queries no longer raise OPERATOR_NOT_ALLOWED_IN_QUERY while MongoDB extended-operator validation remains applicable.