You can write a function that returns the timestamp of your JSON array, and then SELECT it out like that.   Here's an example:


Class Test.JSONProp extends %Persistent

	Property JSON as %String (MAXLEN=1000);
	Property Type as %String;
ClassMethod GetTimestamp(str as %String) as %TimeStamp [SqlProc]
		s j={}.%FromJSON(str)
	    s ts=j.Biometrics.%Get(0).TimeStamp
	    return ts
	catch err
		return ""

The query then becomes:

SELECT ID,Type,Test.JSONProp_GetTimestamp(JSON) as TS 

Give that a try - you'll need to probably do something different with the JSON depending on your version.

What is your StayConnected Value?  0?  If so I would set it to something like 10 and see if that helps.  This is not a problem with the results it's a problem with the connection.  Are you calling ..Adapter.Disconnect()?

I can get you column 2:

 w stmt.%Metadata.columns.GetAt(2).property.SqlFieldName

but I don't see a way to do this with Implicit JOINS (->).   You can get the table and kind of figure it out with something like this:

w stmt.%Metadata.columns.GetAt(2).property.parent.SqlTableName

w stmt.%Metadata.columns.GetAt(2).property.parent.SqlSchemaName

I think the question here is what are you going for in the bigger picture?  Perhaps there's another angle?


There is by no means enough information here to help you.  If this is an urgent problem, you should contact InterSystems Support.  Otherwise, we need a lot more information regarding what your problem is.

Did you open up an issue with the WRC as Brendan recommended?  You should do this if you want this error fixed. You can do so by writing an email to  Please include your table definitions, including all parent/child references that you have (I suspect this is a grandchild table).  

Works for me:


[SQL]SAMPLES>>CALL %SYSTEM.SQL_TableExists('table name')

1. CALL %SYSTEM.SQL_TableExists('table name')

statement prepare time(s)/globals/lines/disk: 0.0398s/1847/15479/4ms

          execute time(s)/globals/lines/disk: 0.0007s/7/159/0ms

                          cached query class: %sqlcq.SAMPLES.cls8

So I don't know what Vitaliy is on about.  What's your version?

In that case, if you EVER want to query the headers (say, you want information on the dates, which would exist in the header) then you will always be loading the children into memory.  This is clearly inefficient.  

You can enforce each Line having a Header by make the property required, and you can use Foreign Keys to cascade deletes as well.  The only time to use Parent-Child is if you will NEVER query the parents without the children (or the tables are to be so small as the performance hit you take doesn't matter).

Parent-Child is not recommended as a solution here.  Because children are stored in the same global as the parent, any time you want to look at parent information, you necessarily have to load in the child rows into your global buffers. 

In the One-To-Many scenario, both the One and Many side have their own tables, so doing SQL lookups should be easy. 

What happens if you run the query in the SQL Shell.  From a terminal execute:

d $SYSTEM.SQL.Shell()

This brings up a prompt which should let you run queries.  Run one and see how long it takes.  You should make sure your system has plenty of global buffers, and your last startup was OK (use the cconsole.log for this).