Running SQL queries on Dynamic objects

Question

Question

Tom Philippi · Dec 13, 2017

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.

Discussion (3)0

Log in or sign up to continue

Robert Cemper · Dec 13, 2017

Proposal:

Store your JSON String as %String(MAXLEN="") like a log file

for the typical components type, age, firstname, lastname, ...
create calculated properties that find its content by $piece, $find or some JSON specific stuff.
setup an index on those calculated properties to find your record.

With this approach you keep the origin as you get it and get te indices you may require.
It's pretty similar to what I did to mimic an XML DB with Caché

so 1) =NO 2) =see above 3) =YES 4) = it's just another challenge

1 0

Steve Pisani · Dec 13, 2017

Hi Tom,

For much of what you requested, you should look at InterSystems IRIS's new Document database model.

The Document databases accepts and persists collections of JSON Documents inside of Cache . Additionally, it allows you to define a set of properties for documents in this document databse. These properties correspond to elements in inserted documents. (ie, you can define the "LastName" as being the element 'lastname') - which effectively updates an index for every document added to the collection that has an element 'lastname'.

You can then perform an SQL query, using any one of these columns, like 'lastname' within the query to restrict/select the JSON documents you are after.

I think this new feature will satisfy your requirements . There is even a generic REST API that allows you to Add/Delete/Update JSON documents.

See: https://learning.intersystems.com/course/view.php?id=687

HTH,

Steve

0 0

score 0 · Answer 1 · 2022-10-21T11:40:43-04:00

Can this be used to temporarily set a dynamic entity to a DocDB so one could run a query on it and return a result set object?

I'm trying to find a solution for returning a result set object to a CSP page when I have a dynamic entity or JSON object.

It's basically a complex, ad hoc query that's easier (for me) to write in ObjectScript rather than write a SQL query and aggregate and transform all kinds of data. I really need something in memory just to run the report, nothing saved to disk, but it seems creating a database using %DocDB will create a new class def weather I like it or not.