Components All New MacOS Windows Linux iOS
Examples Mac & Win Server Client Guides Statistic FMM Blog Deprecated Old

JSON.ReplaceEvaluate

Searches for all values that match the JSONPath expression and replaces them with the result of the evaluation.

Component Version macOS Windows Linux Server iOS SDK
JSON 14.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes ✅ Yes
MBS( "JSON.ReplaceEvaluate"; json; path; Expression )   More

Parameters

Parameter Description Example
json A JSON text or reference. "{\"people\":[{\"first\":\"Christian\",\"last\":\"Schmitz\",\"city\":\"Nickenich\"}]}"
path The path to query. "$.books[?(@.title == 'A Wild Sheep Chase')].price"
Expression The expression to run.
You may call JSON.CurrentMatch function to get current JSON as well as JSON.CurrentMatchPath function to get the path.

Result

Returns JSON or error.

Description

Searches for all values that match the JSONPath expression and replaces them with the result of the evaluation.
Returns an error if JSONPath evaluation fails.

See also JSON.Query to just search without replace and JSON.Replace to replace without expression.
If the expression is not compiling, we return an error. Your expression should return the new JSON to replace the found item. But you can also return empty text to skip this.

Examples

Pass through current match:

MBS("JSON.ReplaceEvaluate"; JSON::JSON1; "$[?(@.PrimaryKey)]"; "Let([json = MBS(\"JSON.CurrentMatch\")]; json")

Replace all found records with 123:

MBS("JSON.ReplaceEvaluate"; JSON::JSON1; "$[?(@.PrimaryKey)]"; "123")

Collect all the match paths on the way:

Let([
$list = "";
r = MBS("JSON.ReplaceEvaluate"; JSON::JSON1; "$[?(@.PrimaryKey)]"; "Let([$list = $list & MBS(\"JSON.CurrentMatchPath\") & \¶]; \"\")")
]; $list)

Collect primary keys we find on the way:

Let([
$list = "";
r = MBS("JSON.ReplaceEvaluate"; JSON::JSON1; "$[?(@.PrimaryKey)]"; "Let([
json = MBS(\"JSON.CurrentMatch\");
key = MBS( \"JSON.GetPathItem\"; json; \"PrimaryKey\"; 1 );
$list = $list & key & \¶
]; \"\")")
]; $list)
// Normally you do this with JSON.Query function more efficiently!

Now search objects with PrimaryKey and add entries for Assignments:

MBS("JSON.ReplaceEvaluate"; JSON::JSON1; "$[?(@.PrimaryKey)]"; "Let([
/*the current match */
$json = MBS(\"JSON.CurrentMatch\");
/* now lookup primary key inside it */
$key = MBS( \"JSON.GetPathItem\"; $json; \"PrimaryKey\"; 1 );
/* look for related records */
$sql = MBS( \"FM.SQL.Execute\"; Get(FileName); \"SELECT Name, Note, PrimaryKey, EmployeeForeignKey, \\\"Date Returned\\\" FROM Assignments WHERE AssetForeignKey = ?\"; $key);
/* now get a JSON array for the found set */
foundJSON = MBS( \"FM.SQL.JSONRecords\"; $sql; \"Name\¶Note\¶AssignmentsPrimaryKey\¶EmployeeForeignKey\¶Date Returned\" );
/* and cleanup memory */
$r = MBS(\"FM.SQL.Release\"; $sql);
/* build new JSON with the related records added */
$newjson = MBS(\"JSON.AddItemToObject\"; $json; \"Assignments\"; foundJSON )
]; $newjson)")

Now search objects with PrimaryKey and add entries for Employees on the new Assignments:

MBS("JSON.ReplaceEvaluate"; JSON::JSON3; "$[*].Assignments[?@.AssignmentsPrimaryKey]"; "Let([
/*the current match */
$json = MBS(\"JSON.CurrentMatch\");
/* now lookup primary key inside it */
$key = MBS( \"JSON.GetPathItem\"; $json; \"EmployeeForeignKey\"; 1 );
/* look for related records */
$sql = MBS( \"FM.SQL.Execute\"; Get(FileName); \"SELECT Name, PrimaryKey, \\\"First Name\\\" FROM Employees WHERE PrimaryKey = ?\"; $key);
/* now get a JSON array for the found set */
foundJSON = MBS( \"FM.SQL.JSONRecords\"; $sql; \"Name\¶PrimaryKey\¶First Name\" );
/* and cleanup memory */
$r = MBS(\"FM.SQL.Release\"; $sql);
/* build new JSON with the related records added */
$newjson = MBS(\"JSON.AddItemToObject\"; $json; \"Employees\"; foundJSON )
]; $newjson)")

Add one to each entry in an array:

MBS( "JSON.ReplaceEvaluate"; "[1,2]"; "$[*]"; " MBS(\"JSON.CurrentMatch\") + 1" )

Example result:
[ 2, 3 ]

See also

Release notes

Blog Entries

This function checks for a license.

Created 30th April 2024, last changed 15th May 2024


JSON.Replace - JSON.ReplaceItemInArray