Filters are what make the function flexible helping you to easily manipulate data.
Filters are added as the second argument of the function
=IMPORTJSON(data_sources, a_list_of_filters)
In case no filter is filled, the function will return all the nodes of the resulting JSON object.
Filters are a powerful tool to navigate into the JSON and display only certain nodes.
To find the right filter, start by returning the full JSON, then find the filter from the 1st column.
Example:
Hence, to retrieve the domain extension of all the countries, you can use:
=IMPORTJSON(B1, "/topLevelDomain/0")
which will return
In this example, IMPORTJSON has detected an array and has automatically pivoted the countries in columns. The first row shows the index for each country. You can combine it with the filter to only return the domain extension for a specific country
=IMPORTJSON(B1, "/0/topLevelDomain/0")
will return
Add several filters
IMPORTJSON accepts a range of filters
Example:
Filters can also be joined in one cell by separating them with a comma
Example:
Use filters to pivot the JSON
Filters can be useful to have elements in a JSON displayed into columns. Indeed, IMPORTJSON will pivot into a table only if all the elements returned are part of the same array. To trigger that, you should filter with the base of the array.
More filter rules
- Absolute path: Only the elements directly descending from the nodes will be listed
/node1/node2
- Relative path: The function will ignore whether the filter is a direct descendant of the root or not
//node2
or use the alias
node2
Widcard: Use an * to replace a chain of characters
node*
will display all the nodes which have names starting with “node”