JSON
Load JSON documents in memory, then select, insert, update and delete elements by path, compare, patch, validate against a schema, flatten and sort.
json to_rows turns an API response into table rows. Typical uses: read a REST response, build a JSON answer for a web service.json loadjson showjson load selectjson load by refjson iobjectjson countjson selectjson is objectjson is arrayjson existjson docjson fieldsjson iarrayjson iarrayjson uarrayjson uobjectjson darrayjson dobjectjson sortjson unloadjson unload_alljson pathjson diffjson patchjson validatejson flattenjson flattenjson unflattenjson unflattenjson to_rowsjson to_rows
json load <key> <jsonString>
Description
To load a JSON string
To load a JSON string
Parameters
key: The key - string - required
jsonString: The JSON string - string - required
key: The key - string - required
jsonString: The JSON string - string - required
admin
json load "keyId" "{}";
mentdb
1
Used in use cases (638 recipes)
MQL beginner › Loop over the lines of a JSON order
MQL developer › Run user formulas safely: eval, try and error codes
Backend & web service developer › Inspect a script and answer with a JSON object
Date & time developer › One row of a date dimension table
String & encoding developer › Encrypt, decrypt and sign a secret
Data validation developer › Supplier onboarding validation report
and 632 more in the Use cases
MQL beginner › Loop over the lines of a JSON order
MQL developer › Run user formulas safely: eval, try and error codes
Backend & web service developer › Inspect a script and answer with a JSON object
Date & time developer › One row of a date dimension table
String & encoding developer › Encrypt, decrypt and sign a secret
Data validation developer › Supplier onboarding validation report
and 632 more in the Use cases
Often used with
json doc · json iarray · json parse_array · script create · string regexp_replace · script delete · execute · file delete
json doc · json iarray · json parse_array · script create · string regexp_replace · script delete · execute · file delete
json show
Description
To show all JSON objects saved in the session
To show all JSON objects saved in the session
admin
json show;
mentdb
["keyId","newKeyId"]
Used in use cases (2 recipes)
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Sort and total a sales array
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Sort and total a sales array
Often used with
json exist · json fields · json is array · json is object · json sort · and · atom before_exclud · atom get_last
json exist · json fields · json is array · json is object · json sort · and · atom before_exclud · atom get_last
json load select <key> <jsonString> <jPath>
Description
To load a JSON string and select an element
To load a JSON string and select an element
Parameters
key: The key - string - required
jsonString: The JSON string - string - required
jPath: The JSON path - string - required
key: The key - string - required
jsonString: The JSON string - string - required
jPath: The JSON path - string - required
admin
json load select "keyId" "{\"a\": 5.0, \"b\": {\"c\": 7.0}}" /a;
mentdb
5.0
Used in use cases (56 recipes)
Backend & web service developer › Inspect a script and answer with a JSON object
String & encoding developer › Match a typed product name to the catalog
JSON & document store developer › Extract and clean a config subtree
Script developer › Patch a script with its generated update request
Execution modes › Stack run postponed until a gate opens
Scheduler developer › A sampling job and statistics on its rolling window
and 50 more in the Use cases
Backend & web service developer › Inspect a script and answer with a JSON object
String & encoding developer › Match a typed product name to the catalog
JSON & document store developer › Extract and clean a config subtree
Script developer › Patch a script with its generated update request
Execution modes › Stack run postponed until a gate opens
Scheduler developer › A sampling job and statistics on its rolling window
and 50 more in the Use cases
Often used with
math round_sig · math sqrt · matrix get · num solve · script delete · stat fit_model · stat mean · complex add
math round_sig · math sqrt · matrix get · num solve · script delete · stat fit_model · stat mean · complex add
json load by ref <fromKey> <jPath> <new KeyId>
Description
To load a part of another JSON document by reference: the new key shares the data, so a change on it also changes the source document (to get a copy, use json load with json select)
To load a part of another JSON document by reference: the new key shares the data, so a change on it also changes the source document (to get a copy, use json load with json select)
Parameters
fromKey: The key - string - required
jPath: The JSON path - string - required
new KeyId: The JSON string - string - required
fromKey: The key - string - required
jPath: The JSON path - string - required
new KeyId: The JSON string - string - required
admin
json load by ref "keyId" /b "newKeyId";
mentdb
{ "c": 7.0 }
admin
json uobject "newKeyId" / c 8 NUM; json select "newKeyId" /c;
mentdb
8.0
admin
json doc "keyId";
mentdb
{ "a": 5.0, "b": { "c": 8.0 } }
Used in use cases (2 recipes)
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Extract and clean a config subtree
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Extract and clean a config subtree
Often used with
json darray · json dobject · json exist · json load select · json unload_all · and · atom before_exclud · atom get_last
json darray · json dobject · json exist · json load select · json unload_all · and · atom before_exclud · atom get_last
json iobject <key> <jPath> <fieldname> <value> <type>
Description
To insert an element into an object in a node
To insert an element into an object in a node
Parameters
key: The key - string - required
jPath: The JSON path - string - required
fieldname: The field name - string - required
value: The value - string - required
type: The type (NUM|INT|STR|BOOL|BOOL2|ARRAY|OBJ) - string - required
key: The key - string - required
jPath: The JSON path - string - required
fieldname: The field name - string - required
value: The value - string - required
type: The type (NUM|INT|STR|BOOL|BOOL2|ARRAY|OBJ) - string - required
admin
json iobject "keyId" / a 5 NUM;
mentdb
1
admin
json iobject "keyId" / tab "[]" ARRAY;
mentdb
1
Used in use cases (50 recipes)
MQL beginner › Loop over the lines of a JSON order
Backend & web service developer › Inspect a script and answer with a JSON object
Date & time developer › One row of a date dimension table
Data validation developer › Supplier onboarding validation report
JSON & document store developer › Build an order document and its ETag
Script developer › Read a script back to build a form
and 44 more in the Use cases
MQL beginner › Loop over the lines of a JSON order
Backend & web service developer › Inspect a script and answer with a JSON object
Date & time developer › One row of a date dimension table
Data validation developer › Supplier onboarding validation report
JSON & document store developer › Build an order document and its ETag
Script developer › Read a script back to build a form
and 44 more in the Use cases
Often used with
json doc · json iarray · string regexp_extract · json uobject · script create · atom get · json parse_obj · script delete
json doc · json iarray · string regexp_extract · json uobject · script create · atom get · json parse_obj · script delete
json count <key> <jPath>
Description
To count the elements in a node
To count the elements in a node
Parameters
key: The key - string - required
jPath: The JSON path - string - required
key: The key - string - required
jPath: The JSON path - string - required
admin
json count "keyId" /
mentdb
3
Used in use cases (206 recipes)
MQL beginner › Loop over the lines of a JSON order
MQL developer › Sort CSV columns: text sort versus numeric sorts
Backend & web service developer › Inspect a script and answer with a JSON object
Date & time developer › GDPR retention: keep, anonymise or delete customers
String & encoding developer › Format a text report like with PHP: sprintf, number_format, natural sort, wordwrap
Data validation developer › Supplier onboarding validation report
and 200 more in the Use cases
MQL beginner › Loop over the lines of a JSON order
MQL developer › Sort CSV columns: text sort versus numeric sorts
Backend & web service developer › Inspect a script and answer with a JSON object
Date & time developer › GDPR retention: keep, anonymise or delete customers
String & encoding developer › Format a text report like with PHP: sprintf, number_format, natural sort, wordwrap
Data validation developer › Supplier onboarding validation report
and 200 more in the Use cases
Often used with
json doc · json iarray · script create · script delete · string regexp_replace · execute · and · file delete
json doc · json iarray · script create · script delete · string regexp_replace · execute · and · file delete
json select <key> <jPath>
Description
To select an element in a node
To select an element in a node
Parameters
key: The key - string - required
jPath: The JSON path - string - required
key: The key - string - required
jPath: The JSON path - string - required
admin
json select "keyId" /a
mentdb
5.0
Used in use cases (590 recipes)
MQL beginner › Loop over the lines of a JSON order
MQL developer › Sort CSV columns: text sort versus numeric sorts
Backend & web service developer › Inspect a script and answer with a JSON object
Date & time developer › Weekly sign-up cohorts and day-14 retention
String & encoding developer › Encrypt, decrypt and sign a secret
Data validation developer › Supplier onboarding validation report
and 584 more in the Use cases
MQL beginner › Loop over the lines of a JSON order
MQL developer › Sort CSV columns: text sort versus numeric sorts
Backend & web service developer › Inspect a script and answer with a JSON object
Date & time developer › Weekly sign-up cohorts and day-14 retention
String & encoding developer › Encrypt, decrypt and sign a secret
Data validation developer › Supplier onboarding validation report
and 584 more in the Use cases
Often used with
json doc · json iarray · string regexp_replace · json parse_array · script create · script delete · execute · file delete
json doc · json iarray · string regexp_replace · json parse_array · script create · script delete · execute · file delete
json is object <key> <jPath>
Description
To check if an element is an object in a node
To check if an element is an object in a node
Parameters
key: The key - string - required
jPath: The JSON path - string - required
key: The key - string - required
jPath: The JSON path - string - required
admin
json is object "keyId" /
mentdb
1
Used in use cases (2 recipes)
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Sort and total a sales array
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Sort and total a sales array
Often used with
json exist · json fields · json is array · json show · json sort · and · atom before_exclud · atom get_last
json exist · json fields · json is array · json show · json sort · and · atom before_exclud · atom get_last
json is array <key> <jPath>
Description
To check if an element is an array in a node
To check if an element is an array in a node
Parameters
key: The key - string - required
jPath: The JSON path - string - required
key: The key - string - required
jPath: The JSON path - string - required
admin
json is array "keyId" /tab
mentdb
1
Used in use cases (2 recipes)
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Sort and total a sales array
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Sort and total a sales array
Often used with
json exist · json fields · json is object · json show · json sort · and · atom before_exclud · atom get_last
json exist · json fields · json is object · json show · json sort · and · atom before_exclud · atom get_last
json exist <key>
Description
To check if a key is loaded
To check if a key is loaded
Parameters
key: The key - string - required
key: The key - string - required
admin
json exist "keyId"
mentdb
1
Used in use cases (4 recipes)
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Sort and total a sales array
The MQL language in depth › Coded errors, nested try and a finally step: a payment pipeline
JSON & document store developer › Extract and clean a config subtree
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Sort and total a sales array
The MQL language in depth › Coded errors, nested try and a finally step: a payment pipeline
JSON & document store developer › Extract and clean a config subtree
Often used with
atom size · json darray · json dobject · json fields · json is array · json is object · json load by ref · json load select
atom size · json darray · json dobject · json fields · json is array · json is object · json load by ref · json load select
json doc <key>
Description
To get a JSON document
To get a JSON document
Parameters
key: The key - string - required
key: The key - string - required
admin
json doc "keyId"
mentdb
{ "a": 5.0, "b": { "c": 8.0 }, "tab": [] }
Used in use cases (117 recipes)
MQL beginner › Your first web service, called with execute
MQL developer › Run user formulas safely: eval, try and error codes
Backend & web service developer › Inspect a script and answer with a JSON object
Date & time developer › One row of a date dimension table
Data validation developer › Supplier onboarding validation report
JSON & document store developer › Build an order document and its ETag
and 111 more in the Use cases
MQL beginner › Your first web service, called with execute
MQL developer › Run user formulas safely: eval, try and error codes
Backend & web service developer › Inspect a script and answer with a JSON object
Date & time developer › One row of a date dimension table
Data validation developer › Supplier onboarding validation report
JSON & document store developer › Build an order document and its ETag
and 111 more in the Use cases
Often used with
json iarray · json iobject · string regexp_replace · json parse_array · atom get · string regexp_extract · script create · execute
json iarray · json iobject · string regexp_replace · json parse_array · atom get · string regexp_extract · script create · execute
json fields <key> <jPath>
Description
To get all the fields of an object in a node
To get all the fields of an object in a node
Parameters
key: The key - string - required
jPath: The JSON path - string - required
key: The key - string - required
jPath: The JSON path - string - required
admin
json fields "keyId" /
mentdb
[ "a", "b", "tab" ]
Used in use cases (19 recipes)
MQL beginner › Loop over the lines of a JSON order
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Sort and total a sales array
Script developer › Copy, rename and delete a module by prefix
Execution modes › execute runs in a new environment
Scheduler developer › Audit your own jobs and generate their redeploy script
and 13 more in the Use cases
MQL beginner › Loop over the lines of a JSON order
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Sort and total a sales array
Script developer › Copy, rename and delete a module by prefix
Execution modes › execute runs in a new environment
Scheduler developer › Audit your own jobs and generate their redeploy script
and 13 more in the Use cases
Often used with
string regexp_replace · json iobject · string contains · json doc · json parse_obj · script create · execute · json iarray
string regexp_replace · json iobject · string contains · json doc · json parse_obj · script create · execute · json iarray
json iarray <key> <jPath> <value> <type>
Description
To insert an element into an array in a node
To insert an element into an array in a node
Parameters
key: The key - string - required
jPath: The JSON path - string - required
value: The value - string - required
type: The type (NUM|INT|STR|BOOL|BOOL2|ARRAY|OBJ) - string - required
key: The key - string - required
jPath: The JSON path - string - required
value: The value - string - required
type: The type (NUM|INT|STR|BOOL|BOOL2|ARRAY|OBJ) - string - required
admin
json iarray "keyId" /tab test STR;
mentdb
1
admin
json iarray "keyId" /tab test2 STR
mentdb
1
admin
json doc "keyId"
mentdb
{ "a": 5.0, "b": { "c": 8.0 }, "tab": [ "test", "test2" ] }
json iarray <key> <jPath> <index> <value> <type>
Description
To insert an element into an array in a node
To insert an element into an array in a node
Parameters
key: The key - string - required
jPath: The JSON path - string - required
index: The index position - number - required
value: The value - string - required
type: The type (NUM|INT|STR|BOOL|BOOL2|ARRAY|OBJ) - string - required
key: The key - string - required
jPath: The JSON path - string - required
index: The index position - number - required
value: The value - string - required
type: The type (NUM|INT|STR|BOOL|BOOL2|ARRAY|OBJ) - string - required
admin
json iarray "keyId" /tab 0 test STR;
mentdb
1
admin
json iarray "keyId" /tab test2 STR
mentdb
1
admin
json doc "keyId"
mentdb
{ "a": 5.0, "b": { "c": 8.0 }, "tab": [ "test", "test", "test2", "test2" ] }
Used in use cases (76 recipes)
MQL developer › Run user formulas safely: eval, try and error codes
Backend & web service developer › Inspect a script and answer with a JSON object
Data validation developer › Supplier onboarding validation report
JSON & document store developer › Build an order document and its ETag
Script developer › Read a script back to build a form
Execution modes › JSON results computed in parallel
and 70 more in the Use cases
MQL developer › Run user formulas safely: eval, try and error codes
Backend & web service developer › Inspect a script and answer with a JSON object
Data validation developer › Supplier onboarding validation report
JSON & document store developer › Build an order document and its ETag
Script developer › Read a script back to build a form
Execution modes › JSON results computed in parallel
and 70 more in the Use cases
Often used with
json doc · string regexp_replace · json parse_array · json iobject · atom get · string regexp_extract · not · script create
json doc · string regexp_replace · json parse_array · json iobject · atom get · string regexp_extract · not · script create
json uarray <key> <jPath> <index> <value> <type>
Description
To update an element in an array in a node
To update an element in an array in a node
Parameters
key: The key - string - required
jPath: The JSON path - string - required
index: The index - integer - required
value: The value - string - required
type: The type (NUM|INT|STR|BOOL|BOOL2|ARRAY|OBJ) - string - required
key: The key - string - required
jPath: The JSON path - string - required
index: The index - integer - required
value: The value - string - required
type: The type (NUM|INT|STR|BOOL|BOOL2|ARRAY|OBJ) - string - required
admin
json uarray "keyId" /tab 1 test2 STR;
mentdb
1
admin
json doc "keyId"
mentdb
{ "a": 5.0, "b": { "c": 8.0 }, "tab": [ "test", "test2", "test2", "test2" ] }
Used in use cases (6 recipes)
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Build an order document and its ETag
Algorithms in MQL › Bubble sort and insertion sort against json sort
IoT & sensor engineer › Remove mains hum from a sensor by FFT
Security engineer › Tamper-evident audit log with a hash chain
Algorithms in MQL › Sieve of Eratosthenes
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Build an order document and its ETag
Algorithms in MQL › Bubble sort and insertion sort against json sort
IoT & sensor engineer › Remove mains hum from a sensor by FFT
Security engineer › Tamper-evident audit log with a hash chain
Algorithms in MQL › Sieve of Eratosthenes
Often used with
json iarray · and · json doc · date current_ms · json iobject · json sort · json uobject · string left
json iarray · and · json doc · date current_ms · json iobject · json sort · json uobject · string left
json uobject <key> <jPath> <fieldname> <value> <type>
Description
To update an element in an object in a node
To update an element in an object in a node
Parameters
key: The key - string - required
jPath: The JSON path - string - required
fieldname: The field name - string - required
value: The value - string - required
type: The type (NUM|INT|STR|BOOL|BOOL2|ARRAY|OBJ) - string - required
key: The key - string - required
jPath: The JSON path - string - required
fieldname: The field name - string - required
value: The value - string - required
type: The type (NUM|INT|STR|BOOL|BOOL2|ARRAY|OBJ) - string - required
admin
json uobject "keyId" / a 8 NUM;
mentdb
1
admin
json doc "keyId"
mentdb
{ "a": 8.0, "b": { "c": 8.0 }, "tab": [ "test", "test2", "test2", "test2" ] }
Used in use cases (14 recipes)
Backend & web service developer › Inspect a script and answer with a JSON object
Date & time developer › Weekly sign-up cohorts and day-14 retention
String & encoding developer › Mail merge: fill a template from JSON data
JSON & document store developer › Build an order document and its ETag
Execution modes › JSON results computed in parallel
SQL developer in MQL › Run MQL on each row with sql parse
and 8 more in the Use cases
Backend & web service developer › Inspect a script and answer with a JSON object
Date & time developer › Weekly sign-up cohorts and day-14 retention
String & encoding developer › Mail merge: fill a template from JSON data
JSON & document store developer › Build an order document and its ETag
Execution modes › JSON results computed in parallel
SQL developer in MQL › Run MQL on each row with sql parse
and 8 more in the Use cases
Often used with
json doc · json iobject · json iarray · string regexp_replace · is null · json fields · string lower · string trim
json doc · json iobject · json iarray · string regexp_replace · is null · json fields · string lower · string trim
json darray <key> <jPath> <index>
Description
To delete an element from an array in a node
To delete an element from an array in a node
Parameters
key: The key - string - required
jPath: The JSON path - string - required
index: The index - integer - required
key: The key - string - required
jPath: The JSON path - string - required
index: The index - integer - required
admin
json darray "keyId" /tab 0;
mentdb
1
admin
json doc "keyId"
mentdb
{ "a": 8.0, "b": { "c": 8.0 }, "tab": [ "test2", "test2", "test2" ] }
Used in use cases (3 recipes)
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Extract and clean a config subtree
Control flow › Drain a print queue with repeat (do-while)
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Extract and clean a config subtree
Control flow › Drain a print queue with repeat (do-while)
Often used with
json dobject · json exist · json load by ref · json load select · json unload_all · and · atom before_exclud · atom get_last
json dobject · json exist · json load by ref · json load select · json unload_all · and · atom before_exclud · atom get_last
json dobject <key> <jPath> <fieldname>
Description
To delete an element from an object in a node
To delete an element from an object in a node
Parameters
key: The key - string - required
jPath: The JSON path - string - required
fieldname: The field name - string - required
key: The key - string - required
jPath: The JSON path - string - required
fieldname: The field name - string - required
admin
json dobject "keyId" / a;
mentdb
1
admin
json doc "keyId"
mentdb
{ "b": { "c": 8.0 }, "tab": [ "test2", "test2", "test2" ] }
Used in use cases (4 recipes)
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Extract and clean a config subtree
Accountant & manager › Bank reconciliation of account 512
JSON & document store developer › Diff the configuration of staging and production
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Extract and clean a config subtree
Accountant & manager › Bank reconciliation of account 512
JSON & document store developer › Diff the configuration of staging and production
Often used with
json darray · json doc · json exist · json fields · json iarray · json load by ref · json load select · json parse_obj
json darray · json doc · json exist · json fields · json iarray · json load by ref · json load select · json parse_obj
json sort <key> <jPath> <field> <order>
Description
To sort an array in a node, by a field of its objects (or by the elements if the field is empty). Numbers are compared as numbers, the other values as strings (case insensitive), null values are at the end
To sort an array in a node, by a field of its objects (or by the elements if the field is empty). Numbers are compared as numbers, the other values as strings (case insensitive), null values are at the end
Parameters
key: The key - string - required
jPath: The JSON path of the array - string - required
field: The field name (empty to sort the elements themselves) - string - required
order: ASC or DESC - string - required
key: The key - string - required
jPath: The JSON path of the array - string - required
field: The field name (empty to sort the elements themselves) - string - required
order: ASC or DESC - string - required
admin
json load "sortId" "[{\"nb\":\"10\"},{\"nb\":\"2\"},{\"nb\":\"9\"}]"; json sort "sortId" / nb ASC;
mentdb
1
admin
json doc "sortId"
mentdb
[ { "nb": "2" }, { "nb": "9" }, { "nb": "10" } ]
Used in use cases (11 recipes)
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Sort and total a sales array
Web service developer › GET: one order, 404, or the list
Web application developer › A JSON endpoint for the page
Algorithms in MQL › Bubble sort and insertion sort against json sort
Industrial quality engineer › Pareto chart of PCB defects with DPMO and sigma level
and 5 more in the Use cases
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Sort and total a sales array
Web service developer › GET: one order, 404, or the list
Web application developer › A JSON endpoint for the page
Algorithms in MQL › Bubble sort and insertion sort against json sort
Industrial quality engineer › Pareto chart of PCB defects with DPMO and sigma level
and 5 more in the Use cases
Often used with
json doc · json iarray · string regexp_replace · json iobject · json parse_array · and · execute · is null
json doc · json iarray · string regexp_replace · json iobject · json parse_array · and · execute · is null
json unload <key>
Description
To unload a JSON document
To unload a JSON document
Parameters
key: The key - string - required
key: The key - string - required
admin
json unload "keyId";
mentdb
1
Used in use cases (451 recipes)
MQL beginner › Loop over the lines of a JSON order
MQL developer › Run user formulas safely: eval, try and error codes
Backend & web service developer › execute, call and include: which environment?
Date & time developer › One row of a date dimension table
String & encoding developer › Encrypt, decrypt and sign a secret
Data validation developer › Supplier onboarding validation report
and 445 more in the Use cases
MQL beginner › Loop over the lines of a JSON order
MQL developer › Run user formulas safely: eval, try and error codes
Backend & web service developer › execute, call and include: which environment?
Date & time developer › One row of a date dimension table
String & encoding developer › Encrypt, decrypt and sign a secret
Data validation developer › Supplier onboarding validation report
and 445 more in the Use cases
Often used with
json doc · json iarray · script create · script delete · string regexp_replace · file delete · json parse_array · execute
json doc · json iarray · script create · script delete · string regexp_replace · file delete · json parse_array · execute
json unload_all
Description
To unload all JSON documents
To unload all JSON documents
admin
json unload_all;
mentdb
1
Used in use cases (2 recipes)
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Extract and clean a config subtree
Backend & web service developer › Inspect a script and answer with a JSON object
JSON & document store developer › Extract and clean a config subtree
Often used with
json darray · json dobject · json exist · json load by ref · json load select · and · atom before_exclud · atom get_last
json darray · json dobject · json exist · json load by ref · json load select · and · atom before_exclud · atom get_last
json path <json> <path>
Description
JSONPath query on a JSON string: $.a.b, $['a'], [0], [-1], [1:3], [*], .*, ..name (recursive), [?(@.field < 10)] (==, !=, <, <=, >, >=, =~ regex); returns the array of the matches
JSONPath query on a JSON string: $.a.b, $['a'], [0], [-1], [1:3], [*], .*, ..name (recursive), [?(@.field < 10)] (==, !=, <, <=, >, >=, =~ regex); returns the array of the matches
Parameters
json: The JSON document - String - required
path: The JSONPath - String - required
json: The JSON document - String - required
path: The JSONPath - String - required
admin
json path "{\"store\": {\"book\": [{\"title\": \"A\", \"price\": 8}, {\"title\": \"B\", \"price\": 12}, {\"title\": \"C\", \"price\": 5}]}}" "$.store.book[?(@.price < 10)].title"
mentdb
["A", "C"]
Recursive
admin
json path "{\"a\": {\"id\": 1, \"b\": {\"id\": 2}}, \"c\": [{\"id\": 3}]}" "$..id"
mentdb
[1, 2, 3]
json diff <json1> <json2>
Description
Differences between two JSON documents as a JSON Patch (RFC 6902) with the old values
Differences between two JSON documents as a JSON Patch (RFC 6902) with the old values
Parameters
json1: The first document - String - required
json2: The second document - String - required
json1: The first document - String - required
json2: The second document - String - required
admin
json diff "{\"name\": \"Ann\", \"age\": 30, \"tags\": [\"a\"]}" "{\"name\": \"Ann\", \"age\": 31, \"tags\": [\"a\", \"b\"], \"city\": \"Paris\"}"
mentdb
[{"op": "replace", "path": "/age", "value": 31, "old": 30}, {"op": "add", "path": "/tags/1", "value": "b"}, {"op": "add", "path": "/city", "value": "Paris"}]
json patch <json> <json_patch>
Description
Apply a JSON Patch (RFC 6902): add, remove, replace, move, copy, test; paths in JSON Pointer (/a/0, - = end of an array)
Apply a JSON Patch (RFC 6902): add, remove, replace, move, copy, test; paths in JSON Pointer (/a/0, - = end of an array)
Parameters
json: The document - String - required
json_patch: The operations - String - required
json: The document - String - required
json_patch: The operations - String - required
admin
json patch "{\"name\": \"Ann\", \"tags\": [\"a\"]}" "[{\"op\": \"replace\", \"path\": \"/name\", \"value\": \"Anna\"}, {\"op\": \"add\", \"path\": \"/tags/-\", \"value\": \"b\"}, {\"op\": \"add\", \"path\": \"/city\", \"value\": \"Lyon\"}]"
mentdb
{"name": "Anna", "tags": ["a", "b"], "city": "Lyon"}
json validate <json> <json_schema>
Description
Validate a JSON document with a JSON Schema (draft 7: type, required, properties, additionalProperties, items, enum, const, minimum, maximum, minLength, maxLength, pattern, format, minItems, maxItems, uniqueItems, allOf, anyOf, oneOf, not): valid and the list of the errors
Validate a JSON document with a JSON Schema (draft 7: type, required, properties, additionalProperties, items, enum, const, minimum, maximum, minLength, maxLength, pattern, format, minItems, maxItems, uniqueItems, allOf, anyOf, oneOf, not): valid and the list of the errors
Parameters
json: The document - String - required
json_schema: The schema - String - required
json: The document - String - required
json_schema: The schema - String - required
admin
json validate "{\"id\": \"x\", \"email\": \"ann@mail\", \"age\": 150}" "{\"type\": \"object\", \"required\": [\"id\", \"name\"], \"properties\": {\"id\": {\"type\": \"integer\"}, \"email\": {\"type\": \"string\", \"format\": \"email\"}, \"age\": {\"type\": \"integer\", \"minimum\": 0, \"maximum\": 120}}}"
mentdb
{"valid": false, "errors": [{"path": "/name", "message": "is required"}, {"path": "/id", "message": "must be of type integer"}, {"path": "/email", "message": "must be a valid email"}, {"path": "/age", "message": "must be <= 120"}]}
json flatten <json>
Description
Flatten a JSON document: one key per value (a.b, a.list.0...)
Flatten a JSON document: one key per value (a.b, a.list.0...)
Parameters
json: The document - String - required
json: The document - String - required
admin
json flatten "{\"a\": {\"b\": 1, \"c\": [2, 3]}, \"d\": \"x\"}"
mentdb
{"a.b": 1, "a.c.0": 2, "a.c.1": 3, "d": "x"}
json flatten <json> <separator>
Description
Flatten with another separator
Flatten with another separator
Parameters
json: The document - String - required
separator: The separator - String - required
json: The document - String - required
separator: The separator - String - required
admin
json flatten "{\"a\": {\"b\": 1, \"c\": [2, 3]}}" "_"
mentdb
{"a_b": 1, "a_c_0": 2, "a_c_1": 3}
json unflatten <json>
Description
Rebuild a JSON document from flat keys (the numeric parts are array indexes)
Rebuild a JSON document from flat keys (the numeric parts are array indexes)
Parameters
json: The flat object - String - required
json: The flat object - String - required
admin
json unflatten "{\"a.b\": 1, \"a.c.0\": 2, \"a.c.1\": 3, \"d\": \"x\"}"
mentdb
{"a": {"b": 1, "c": [2, 3]}, "d": "x"}
json unflatten <json> <separator>
Description
Rebuild with another separator
Rebuild with another separator
Parameters
json: The flat object - String - required
separator: The separator - String - required
json: The flat object - String - required
separator: The separator - String - required
admin
json unflatten "{\"a_b\": 1, \"a_c_0\": 2}" "_"
mentdb
{"a": {"b": 1, "c": [2]}}
json to_rows <json>
Description
Turn a JSON document (array of objects, API response) into rows: nested objects are flattened (customer.name), arrays become JSON texts, all the rows get the same columns (null when missing)
Turn a JSON document (array of objects, API response) into rows: nested objects are flattened (customer.name), arrays become JSON texts, all the rows get the same columns (null when missing)
Parameters
json: The JSON document - String - required
json: The JSON document - String - required
admin
json to_rows "[{\"id\": 1, \"customer\": {\"name\": \"Durand\", \"city\": \"Lille\"}}, {\"id\": 2, \"vip\": true}]"
mentdb
[{"id": 1, "customer.name": "Durand", "customer.city": "Lille", "vip": null}, {"id": 2, "customer.name": null, "customer.city": null, "vip": true}]
json to_rows <json> <json_params>
Description
Turn a JSON document into rows with parameters: path (JSONPath of the array, $ by default), explode (a nested array: one row per element, the parent fields are repeated, the fields of the element are prefixed), keep_empty (true: an empty exploded array keeps one row), arrays (json | join: scalars joined with ", " | index: a.0.b), separator (.), columns (order and subset), rename ({"old": "new"}, two columns cannot get the same name)
Turn a JSON document into rows with parameters: path (JSONPath of the array, $ by default), explode (a nested array: one row per element, the parent fields are repeated, the fields of the element are prefixed), keep_empty (true: an empty exploded array keeps one row), arrays (json | join: scalars joined with ", " | index: a.0.b), separator (.), columns (order and subset), rename ({"old": "new"}, two columns cannot get the same name)
Parameters
json: The JSON document - String - required
json_params: The parameters - String - required
json: The JSON document - String - required
json_params: The parameters - String - required
admin
json to_rows "{\"data\": [{\"id\": 1, \"customer\": {\"name\": \"Durand\", \"city\": \"Lille\"}, \"items\": [{\"sku\": \"A1\", \"qty\": 2}, {\"sku\": \"B7\", \"qty\": 1}], \"tags\": [\"new\", \"web\"]}, {\"id\": 2, \"customer\": {\"name\": \"Leroy\", \"city\": \"Nantes\"}, \"items\": [], \"tags\": []}], \"next\": null}" "{\"path\": \"$.data\", \"explode\": \"items\", \"arrays\": \"join\"}"
mentdb
[{"id": 1, "customer.name": "Durand", "customer.city": "Lille", "tags": "new, web", "items.sku": "A1", "items.qty": 2}, {"id": 1, "customer.name": "Durand", "customer.city": "Lille", "tags": "new, web", "items.sku": "B7", "items.qty": 1}, {"id": 2, "customer.name": "Leroy", "customer.city": "Nantes", "tags": null, "items.sku": null, "items.qty": null}]
Selected and renamed columns, customers without items dropped
admin
json to_rows "{\"data\": [{\"id\": 1, \"customer\": {\"name\": \"Durand\", \"city\": \"Lille\"}, \"items\": [{\"sku\": \"A1\", \"qty\": 2}, {\"sku\": \"B7\", \"qty\": 1}], \"tags\": [\"new\", \"web\"]}, {\"id\": 2, \"customer\": {\"name\": \"Leroy\", \"city\": \"Nantes\"}, \"items\": [], \"tags\": []}], \"next\": null}" "{\"path\": \"$.data\", \"explode\": \"items\", \"keep_empty\": false, \"columns\": [\"id\", \"customer.name\", \"items.sku\", \"items.qty\"], \"rename\": {\"customer.name\": \"customer\", \"items.sku\": \"sku\", \"items.qty\": \"qty\"}}"
mentdb
[{"id": 1, "customer": "Durand", "sku": "A1", "qty": 2}, {"id": 1, "customer": "Durand", "sku": "B7", "qty": 1}]
Used in use cases (2 recipes)
XML, HTML & Word integrator › Receive an XML order file: validate it against its XSD, convert it to JSON rows and to YAML
Integrator & ETL developer › From a REST API answer to an Excel report: nested orders flattened into lines
XML, HTML & Word integrator › Receive an XML order file: validate it against its XSD, convert it to JSON rows and to YAML
Integrator & ETL developer › From a REST API answer to an Excel report: nested orders flattened into lines
Often used with
excelx read · excelx sheets · excelx write · file delete · file exist · file mkdir · json to_yaml · table group_by
excelx read · excelx sheets · excelx write · file delete · file exist · file mkdir · json to_yaml · table group_by
