Skip to Main Content
HCL Domino Ideas Portal

Welcome to the #dominoforever Product Ideas Forum! The place where you can submit product ideas and enhancement request. We encourage you to participate by voting on, commenting on, and creating new ideas. All new ideas will be evaluated by HCL Product Management & Engineering teams, and the next steps will be communicated. While not all submitted ideas will be executed upon, community feedback will play a key role in influencing which ideas are and when they will be implemented.

For more information and upcoming events around #dominoforever, please visit our Destination Domino Page

Status Assessment
Workspace Domino Designer
Categories @Formulas
Created by Guest
Created on Sep 17, 2020
Merged idea

This idea has been merged into another idea. To comment or vote on this idea, please visit DDXP-I-661 Support JSON in @Formula language.

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