Filters

Filters are added as the second parameter 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 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”