Add @Formulas to parse JSON strings Merged

Web applications heavily use data in JSON format, in many cases with deeply nested structures. In our applications, we store JSON structures in text fields (Domino R12 will extend the max size of text items, so that there's just an overall limit of 16 MB summary data per doc), but Domino currently has no built-in support to show JSON field values in view columns or use it in NSF searches.

Just mapping JSON key/value pairs to document items 1:1 is not sufficient, because the JSON structures are often deeper than just one level.

Let's say a document contains a text item "myjsonfield" with the following JSON content:

{
	"firstname": "Peter",
	"lastname": "Miller",
	"keywords" : [ "important", "sendnewsletter" ],
	"companies" : [
		{ "label": "ACME", "id": "ID1" },
		{ "label": "HCL", "id" : "ID2" }
	],
	"birthday" : {
		"year" : 1979,
		"month" : 5,
		"day" : 26
	},
	"address": {
		"home": {
			"street": "Test street 5",
			"city": "New York"
		}
	}
}

Please add the following formulas so that we can easily parse/extract parts of the JSON object (listed with expected values):

// extract single JSON value via their path in the tree structure:
@JsonValue(myjsonfield; "address.home.street”) == "Test street5"
@JsonValue(myjsonfield; "birthday.year”) == 1979
@JsonValue(myjsonfield; "keywords”) == "[\"important\" : \"sendnewsletter\" ]"
@JsonValue(myjsonfield; "keywords[0]”) == "important"
@JsonValue(myjsonfield; "address.home") == "{ \"street\" : \"Test street 5\", \"city\": \"New York\" }"

// get the type of JSON structure values:
@JsonValueType(myjsonfield; "”) == „jsonobject”
@JsonValueType(myjsonfield; "address.home.street”) == „string”
@JsonValueType(myjsonfield; "address.home”) == „jsonobject”
@JsonValueType(myjsonfield; "birthday.year") == „number”
@JsonValueType(myjsonfield; "companies”) == „jsonarray”
@JsonValueType(myjsonfield; "companies[0]”) == „jsonobject”

// get the length of JSON arrays:
@JsonEntries(myjsonfield; "keywords") = 2

// get a single JSON array item:
@JsonEntry(myjsonfield; "keywords"; 0) = "important";
@JsonEntry(myjsonfield; "keywords"; 1) = "sendnewsletter";

@JsonEntry(myjsonfield; "companies"; 0) = "{ \"label\" : \"ACME\", \"id\" : \"ID1\" }";
@JsonEntry(myjsonfield; "companies"; 1) = "{ \"label\" : \"HCL\", \"id\" : \"ID2\" }";

// get all keys of JSON objects
@JsonKeys(myjsonfield; "address.home”) == "street” : "city”

@JsonKeys(myjsonfield; "”) == "firstname” : "lastname" : "companies" : "birthday" : "address"

// return all available JSON paths of subtrees so that we can use them in @transform to traverse the whole JSON structure:
@JsonPaths(myjsonfield; "") = "firstname" : "lastname" : "keywords" : "companies" : "birthday" : "birthday.year", "birthday.month" : "birthday.day", "address" : "address.home" : "address.home.street" : "address.home.city"

@JsonPaths(myjsonfield; "address") = "home" : "home.street" : "home.city"

Karsten Lehmann, Mindoo GmbH