Running SQL queries on Dynamic objects
Recently viewed a demo on the new Dynamic Objects in InterSystems (we are still running 2016.1); together with the upcoming IRIS data platform I started thinking about possibilities of building dynamic databases. That is, I can imagine some projects were it might be really nice to store dynamic objects and then run sql queries on them without ever defining the fields of the dynamic objects (i.e. not at storage, but only if you run your sql query). If there is any system where this might be possible it is InterSystems Cache. Of course, Ithis might not be a good idea for an entirety of reasons, but one can still dream, so please bear with me :P.
So the core idea is to host an intersystems REST API that acepts any JSON message, converts it to a dynamic object and store it in Cache. Then I'd like to run a sql query on the stored objects to see what I actually received.
So here are my questions:
1. Is it actually possible to store a dynamic object without ever specifiying the properties of the persistent object?
2. If not, can we think of a way to actually achieve/mimic this behaviour.
3. Would it be then possible to run a sql query on it as specified in the example below.
4. Why is this a good idea/ not good idea and what would be good alternatives.
An example:
Suppose I receive the following json messages:
{"type":"person", "firstname":"John", "age": 12}
{"type":"person", "firstname":"Alex", "lastname", "Smith", "age": 60}
{"type":"address", "street" : "diagonally", "housenumber": "12"}
Now i'd like to know the first and lastname of the people older than fifty: SELECT firstname, lastname FROM StoredDynamicObjectsTable WHERE (type = person AND age < 50) and it should return a table with one row containing ALEX SMITH.