How do I make search based on some property in JSON in Notes View

Question

Suppose I have a NotesDocument with a property named "someJSONObject" with the following value:

"{
 "someObject": 
  {
   "objId": "someId"
   "Object_Name": "objName",
   "Alternative_Name" : "altName",
   "Alias_Name" : "alias"
  }
}"

My question is how I can get "objId" property to use it in Notes View in such a way, so I would be able to use this as a seach criteria. There are only "Simple Function", "Field", "Formula" available. How can I write it there, instead of writing duplicate field just for sorting purposes?

score 0 · Answer 1 · answered Mar 22 '18 at 09:06

Here are some lines to do it with formula: First split the values into an array with @Explode, then find the line / lines with "objID" and get the values from them. This code even works, if there are multiple objID- Tags in JSON.

_allVals := @Explode( someJSONObject; @Newline );
REM "Depending of the origin of your data, you need to replace @NewlIne by @Char( 10 ) , @Char( 13 ) or a combination of them";

_objIDString := @Trim( @Right( _allVals ) ; {"objId": } ) );
_objID := @LeftBack( @Right( _objIDString ; {"} ) ; {"} );
_objID

Just one remark: After saving the curly brackets will be replaced by " and all " inside them will be masked with "\".

This code: @Right( _objIDString ; {"} ) will then look like this @Right( _objIDString ; "\"" )

This is done for downwards compatibility as older versions of notes don't understand the curly brackets.

Don't you know how to get every "expertID" here? Inside allExperts array? { "allAssignedExperts" : { "assignmentDate" : "someDateHere", "allExperts" : [ { "expertId" : "someId", "expertNameSurname" : "someNameHere", "didExceedDeadline" : "false", "isLead" : "false" }, { "expertId" : "anotherId", "expertNameSurname" : "anotherNameHere", "didExceedDeadline" : "true", "isLead" : "true" } ] } } Please use this service to make it look pretty https://jsonformatter.curiousconcept.com/ — J.Nick, Mar 22 '18 at 11:20

Knut Herrmann · Answer 2 · 2018-03-22T14:03:36.770

You are already using JSON LotusScript Classes in your project.

Create a LotusScript agent which

runs on all your documents with the JSON field and
extracts the needed values with the help of JSON LotusScript Classes and
puts them in separate document's fields
which can be used in view's columns

In case you write the JSON field with some LotusScript code already you could add the field separation right there.

How do I make search based on some property in JSON in Notes View

2 Answers2