ajJSONPath function

Description

The ajJSONPath gets the result of a JSON path expression based on a given JSON text.

Syntax

ajJSONPath(json_data, json_path, [return_type], [data_name], [convert_to_text],[run_by_function_point_only])

Argument Name	Argument Type	Description
json_data (required)	Range / Array	The range of cells that contains the JSON string. The range of cells can span across multiple rows and columns. The cell values will be concatenated into one string from left to right and top to bottom.
json_path (required)	String	The criteria in JSON Path format. For example, $.Employee[].ID[]
return_type (optional)	Double	If return_type equals 1, the result will be a JSON string. It is often used with another AlchemyJ Extended Function that takes a JSON string as its input. For example, ajJSONToTable. If return_type equals 0, the filtered JSON string can span across the cell arrays. It is normally used to further use/process the data. The default value is 0.
data_name (optional)	String	The name of the returned JSON text. It only applies when return_type is 1. The default value is "data".
convert_to_text (optional)	Boolean	If it equals TRUE, the return result will be converted to string values. If it equals FALSE, the return result will preserve the original data type. The default value is FALSE.
run_by_function_point_only (optional)	Boolean	If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE.

The function will return:

1) Return Value: Search result value, the search result in JSON format

2) Return Type: Single Value / Multiple values (array formula)

Example

Example 1

The JSON input below contains an array of Employee which has two records.

The formula takes B1:C14 as the JSON data to be searched. $.Employee[*] means to return all elements in the Employee array. As return_type is empty, it returns the result as a cell array by default.

Example 2

The JSON data is the same as Example 1, it will filter the JSON node 'First Name' for all records. A JSON string was returned since the return type is 1 in this example.

Click here to download the use case workbooks for further reference.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
Invalid return type, it can only be 0 or 1.
Invalid JSON Format
JSON data contains an Excel error.
JSON data contains a node name with space in it but the JSON path does not wrapped by '[]'. For example, [Product Name].