SQL Search index on JSON objects.

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

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

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 :)

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;