Question
Güvenal · Nov 10

SQL Search index on JSON objects.

Hello there!
I am hoping to get some help with "SQL Search index" and JSON objects.
I am storing a JSON object in a column of type %Stream.GlobalCharacter 
Property JSON As %Stream.GlobalCharacter;
I also have a index that looks like below.
Index AnalyticIdx On (JSON) As %iFind.Index.Analytic(INDEXOPTION = 0, LANGUAGE = "en", LOWER = 1);
The index is updated on insert, but the property names are indexed as well, like firstName in a object like {"FirstName":"Magnus", "LastName":"Guvenal"}, Say I want "Magnus" ie FirstName och not "Güvenal" / LastName to be indexed.
Is that supported, if so am I using the wrong datatype for the JSON object?

Link to documentation 
 

Best regards,
Magnus

Product version: HealthShare 2020.1
$ZV: IRIS for Windows (x86-64) 2020.1 (Build 215U) Mon Mar 30 2020 20:14:33 EDT [HealthConnect:1.1.1]
10
3 0 20 184
Log in or sign up to continue

Just an idea:
If you use calculated properties (==columns) for FirstName, LastName
you can create an index on these properties without increasing the storage requirement.
 

Hello Robert!
The JSON object can be a number of different objects with different properties.
So I was thinking that the indexing logic would be smart enough to identify for example address in {"address":"Mystreet", "city":"MyCity"} and only index that column.
Make sense?
Best regards,
Magnus

OK. I was blocked by other activities. But this is my solution:
Assumption1:    Property JSON As %Stream.GlobalCharacter;
Assumption2:    You know the properties you want to index, as with normal tables 
The idea: A calculated property is mainly used for building indices 
The solution: the Stream needs to be presented as %DynamicObject to get the value.
 And here is it:

Property JSON As %Stream.GlobalCharacter;

Property FirstName As %String [ Calculated, SqlComputed ,
         SqlComputeCode = { set {*}=..GetDyn({ID},"FirstName") } ];

Property LastName As %String [ Calculated, SqlComputed ,
         SqlComputeCode = { set {*}=..GetDyn({ID},"LastName") } ];

Index fn on FirstName;
Index ln on LastName;

ClassMethod GetDyn(ID As %Integer, item = "") As %String
{
   set JSON=..%OpenId(ID).JSON
   do JSON.Rewind()
   set st=JSON.Read(3000000)
   set dyno={}.%FromJSON(st)
   set rep=$Property(dyno,item)
   quit rep
}

There is room to improve the speed of the method.    
Also saving your keys in individual properties during data load could be a valid approach.
The principle is always the same. %Stream --> %DynamicObject --> extract keys by name

1. Implement %iFind.Transformation.Abstract interface with the custom transformation that would remove all property names (I assume you know property names beforehand).

After that set TRANSFORMATIONSPEC index parameter to your transformation implementation.

2. Maybe User Dictionaries could also be used.

3. Are you sure you need analytic queries? Simpler iFind indices (Minimal, Basic) can be advantageous in some situations.

Calling @Benjamin De Boe 
 

an iFind transformation would be helpful if you would sometimes want to query the non-transformed form as well, which I don't think is the case here. Just a computed field and/or @Timothy Leavitt s excellent BuildValueArray() sample would be a simple and effective solution.

Maybe not simpler, and definitely more complicated if you use a stream rather than a string with MAXLEN="" (which I'll demo below); here's what it ends up looking like:

Class DC.Demo.JSON Extends %String [ ClassType = datatype ]
{

Parameter MAXLEN;

/// Builds value array with subscripts set to values of properties in the JSON stream.
ClassMethod BuildValueArray(value As DC.Demo.JSON, ByRef valueArray As %String) As %Status [ Private ]
{
    set sc = $$$OK
    try {
        set object = {}.%FromJSON(value)
        do ..AddObjectToValueArray(object,.valueArray)
    } catch e {
        set sc = e.AsStatus()
    }
    quit sc
}

ClassMethod AddObjectToValueArray(object As %DynamicAbstractObject, ByRef valueArray, truncateToLength As %String = 255)
{
    set iter = object.%GetIterator()
    while iter.%GetNext(.key,.value) {
        if $isobject(value) {
            do ..AddObjectToValueArray(value,.valueArray)
        } else {
            set sub = $extract(value,1,truncateToLength)
            set valueArray(sub) = ""
        }
    }
}

}

Class DC.Demo.IndexJSON Extends %Persistent
{

Property JSON As JSON(MAXLEN = "");

Index JSONValues On JSON(KEYS);

ClassMethod Run()
{
    try {
        do ..%KillExtent()
        for json = {"FirstName":"Magnus", "LastName":"Guvenal"},
        ["Magnus", "Guvenal"],
        {
            "a":{"b":{"c":"Magnus"
            } }} {
            set inst = ..%New()
            set inst.JSON = json.%ToJSON()
            $$$ThrowOnError(inst.%Save())
        }
        do ..DisplaySQL("select JSON from DC_Demo.IndexJSON where for some %ELEMENT(JSON) (%KEY = 'Magnus')")
        do ..DisplaySQL("select JSON from DC_Demo.IndexJSON where for some %ELEMENT(JSON) (%KEY = 'Guvenal')")
        do ..DisplaySQL("select JSON from DC_Demo.IndexJSON where for some %ELEMENT(JSON) (%KEY = 'FirstName')")
    } catch e {
        set sc = e.AsStatus()
        write !,$system.Status.GetErrorText(sc)
    }
}

ClassMethod DisplaySQL(query, args...)
{
    write !,query,!
    for i=1:1:$get(args) {
        write "argument: ",args(i),!
    }
    do ##class(%SQL.Statement).%ExecDirect(,query,args...).%Display()
}

Storage Default
{
<Data name="IndexJSONDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>JSON</Value>
</Value>
</Data>
<DataLocation>^DC.Demo.IndexJSOND</DataLocation>
<DefaultData>IndexJSONDefaultData</DefaultData>
<IdLocation>^DC.Demo.IndexJSOND</IdLocation>
<IndexLocation>^DC.Demo.IndexJSONI</IndexLocation>
<StreamLocation>^DC.Demo.IndexJSONS</StreamLocation>
<Type>%Storage.Persistent</Type>
}

}

In action:

USER>d ##class(DC.Demo.IndexJSON).Run()
 
select JSON from DC_Demo.IndexJSON where for some %ELEMENT(JSON) (%KEY = 'Magnus')
JSON
{"FirstName":"Magnus","LastName":"Guvenal"}
["Magnus","Guvenal"]
{"a":{"b":{"c":"Magnus"}}}
 
3 Rows(s) Affected
select JSON from DC_Demo.IndexJSON where for some %ELEMENT(JSON) (%KEY = 'Guvenal')
JSON
{"FirstName":"Magnus","LastName":"Guvenal"}
["Magnus","Guvenal"]
 
2 Rows(s) Affected
select JSON from DC_Demo.IndexJSON where for some %ELEMENT(JSON) (%KEY = 'FirstName')
JSON
 
0 Rows(s) Affected

Hello Timothy and thank you for your answer.
With your suggested method do I have to "manually" update the index after every insert/update to the "table" by calling
d ##class(DC.Demo.IndexJSON).Run() ?
Best regards,

Magnus

 

No, the index that uses BuildValueArray is updated automatically after %Save()/insert/update/etc. The Run() method just demonstrates how the index works, it doesn't do anything special or index-specific.

Good morning all!
Whaow thank you for your time and effort! You all gave me a great start of a new day angel


I cannot still help to wonder about the few lines found in the documentation, how to you interpret these lines below.
"Indexing a JSON Object
You can create an iFind index for text stored in a JSON object. This index specifies the starting position in the JSON structure. iFind recursively indexes all text at that level and all nested levels below it. Specify $ to index the entire JSON object. Specify $.key2 to index the JSON values at key2 and below."

Link to documentation

So using my sample json I am thinking that it means that I can choose only to index firstName by creating the index and passing an argument like $.firstName something like 

Index AnalyticIdx On (JSON) As %iFind.Index.Analytic(KEY=$.firstName, INDEXOPTION = 0, LANGUAGE = "en", LOWER = 1);

Best regards,
Magnus

There is a basic mistake:
docs refer to "Indexing a JSON Object aka  %DynamicObject 
But in your example, you use %Stream.GlobalCharacter  which is a totally different object
The fact that it contains a JSON formatted string is not visible from the outside of the stream.
Instead of writing it to the Stream (which is an overkill anyhow) convert it and store it as JSON Object. 
That's what I had to do in my example

Hello Robert!
ok I think I understand, thank you again for your time and code.

So in my first attempt I actually saved the object in the databas as a %DynamicObject but I could not get the indexing to work.
So saving as a %DynamicObject is not problem I am in control of how the information is saved.
Lets say JSON contains "firstName" how would i create the index directly without computed properties on just that property $.firstName

This below did not work and as far as I can tell it did not even understand that it was a object and just indexed the refrerence.
Index AnalyticIdx On (JSON) As %iFind.Index.Analytic(INDEXOPTION = 0, LANGUAGE = "en", LOWER = 1);
 

So I am still confused by this line below from the docs

Specify $ to index the entire JSON object. Specify $.key2 to index the JSON values at key2 and below...

ok but where should I specify $ or $.firstName, please answer if you know this.

Best regards,
Magnus

"ok but where should I specify $ or $.firstName, please answer if you know this."

Sorry, I'm lost as you. 
From the docs I understand this is a position definition. 
Though I have no idea how to apply it to the %iFind.

Hello Robert!
Thank you at least I find comfort in that you understand me and that I am not alone :-)
I have marked your previous solution as the answer, since it works and is what I ended up using.

Best regards,

Magnus
 

Whoa - I had no idea iFind had JSON indexing features. That said, the docs aren't really helpful here. I'll issue another call for @Benjamin De Boe to chime in :)

hmm, I believe that's a feature of DocDB that crept into the wrong (or at least a confusing) part of the documentation. I'll follow up internally. Thanks for the note.

You can use this syntax on calculated properties:

/// This property holds the document state. Serialization is JSON. 
Property JSON As %Library.DynamicAbstractObject;

/// This is an automatically calculated property "$.firstName"
Property firstName As %VarString [ SqlComputeCode = { set {*}=$$%EvaluatePathOne^%DocDB.Document({JSON},"$.firstName")}, SqlComputed, SqlComputeOnChange = %Doc ];

/// Index on firstName property
Index firstName On firstName;