· Nov 10, 2021

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,

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]
Discussion (20)2
Log in or sign up to continue

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 

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"],
            } }} {
            set inst = ..%New()
            set inst.JSON = json.%ToJSON()
        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 name="2">


In action:

USER>d ##class(DC.Demo.IndexJSON).Run()
select JSON from DC_Demo.IndexJSON where for some %ELEMENT(JSON) (%KEY = 'Magnus')
3 Rows(s) Affected
select JSON from DC_Demo.IndexJSON where for some %ELEMENT(JSON) (%KEY = 'Guvenal')
2 Rows(s) Affected
select JSON from DC_Demo.IndexJSON where for some %ELEMENT(JSON) (%KEY = 'FirstName')
0 Rows(s) Affected

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,

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,

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;