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?
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]
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:
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.
Another simpler option would just be using BuildValueArray - see e.g. https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls... (code sample coming...)
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:
In action:
We've done something similar to this for indexing with JSON paths (in the style of https://goessner.net/articles/JsonPath/) as the keys and the value at that path as the value.
Good idea!
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
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
THX !
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:
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue