Simplified JSON SQL Queries

Inspired by @Ashok Kumar T's post on the ideas portal here as well as my own wishes for a solution to this problem, I have come up with a simple way to allow more complete and consistent JSON queries without having to specify every desired field. I have created a class that extends the built-in %JSON.Adaptor class and makes its %JSONExportToString and %JSONExportToStream methods accessible through SQL with just a couple of simple SqlProc Methods.

Class DH.JSONAdaptor Extends %JSON.Adaptor [ Abstract ]
{
ClassMethod jsonstring(id, map = "") [ SqlProc ]
{
	try{
		set myobj = ..%OpenId(id)
		set sc = myobj.%JSONExportToString(.json,map)
		if $$$ISERR(sc) $$$ThrowStatus(sc)
		return json
	}
		catch ex{
		throw ex
	}
}

ClassMethod jsonstream(id, map = "") [ SqlProc ]
{
	try{
		set myobj = ..%OpenId(id)
		set sc = myobj.%JSONExportToStream(.json,map)
		if $$$ISERR(sc) $$$ThrowStatus(sc)
		return json
	}
	catch ex{
		throw ex
	}
}
}

This allows the user to get the results of those methods into columns in an SQL query very efficiently. For example, suppose we have the following class:

Class User.Name Extends (%Persistent, DH.JSONAdaptor)
{

XData NoMiddle
{
<Mapping xmlns = "http://www.intersystems.com/jsonmapping" >
	<Property Name="First" FieldName="First" />
	<Property Name="Last" FieldName="Last" />
</Mapping>
}

Property First As %String;

Property Middle As %String;

Property Last As %String;
}

Then we could query as follows, providing the id column to the SqlProc to get out the default JSON object mapping:

Or, since we have an additional JSON mapping defined in this class, we can also provide the optional map argument to use that mapping instead:

While this isn't a perfect solution the the idea suggested since it does require the class to extend this class, I believe it is a step in the right direction, and it wouldn't hurt for this to be included in the built-in %JSON.Adaptor in the future.

This package is available here on Open Exchange, or you can install it in the InterSystems Package Manager by using "install sqljsonadaptor".