ajFilterJson function
Description
The ajFilterJson gets the result of a JSON path expression based on a given JSON text.
Syntax
ajFilterJson(json_data, json_path, [return_type], [data_name], [convert_to_text])
| Argument Name | Description |
|---|---|
| json_data (required) | 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) | Specify the criteria in JSON Path format. For example, $.Employee[].ID[] |
| return_type (optional) | 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, ajPopulateJsonToTable. 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. If you do not specify anything, the default value will always be 0. |
| data_name (optional) | Specify the name of the returned JSON text. It only applies when return_type is 1. If you do not specify anything, the default value will always 'data'. |
| convert_to_text (optional) | 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 but any string value that has more than 256 characters will cause an error. If you do not specify anything, the default value will always be FALSE. |
The function will return:
1) Content type: Search result value, the search result in JSON format
2) Method: Within a cell / row cell array
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.