PartiQL on DynamoDB is just syntactic sugar. DynamoDB is not meant to be used that way, but it's query and insert language can be a bit tricky, PartiQL just a helper for that weird query syntax, you can't do joins or aggregates. PartiQL is supported by QLDB, Redshift as well, to unify a querying language somewhat. Docs should be much more clearer to indicate the dangers of it , to avoid confusion like this.

But you can scan MongoDB, Elastic with Presto in parallel it works great when you need to run it, a few times. But if you find yourself using a NoSQL data store as relational, or OLTP cases just because you have ability to run SQL on it is going to hurt you and it should be obvious. As with everything in software, it depends.

We use PartiQL library directly at Resmo https://www.resmo.com because it makes querying the datastore with nested values easier and the its storage independent.