Filtering a JSON array

The examples below use a dataset too large to display fully here. The topic is easier to follow if you download and open THIS EXAMPLE PROJECT, paste the example ATL into a script, and click Preview to see results.

The full dataset is an array — "yr2021" — of 96 JSON objects. For reference, the first three are:

{
    "yr2021": [
        {
            "ID": 1,
            "city": "Atlanta",
            "region": "Southeast",
            "month": "Jan",
            "quarter": "Q1",
            "sales": 56000,
            "target": 50000
        },
        {
            "ID": 2,
            "city": "Boston",
            "region": "Northeast",
            "month": "Jan",
            "quarter": "Q1",
            "sales": 32000,
            "target": 40000
        },
        {
            "ID": 3,
            "city": "Chicago",
            "region": "Midwest",
            "month": "Jan",
            "quarter": "Q1",
            "sales": 44000,
            "target": 45000
        }
    ]
}

Suppose you want to calculate the total sales value for Q2. The first step is to get all objects for which the quarter value is Q2. You can achieve this with a single-field filter.

Paste this ATL into the example project and click Preview. The result is a filtered array containing Objects 25–48 (inclusive). Once you have a filtered array, you can use other ATL functions — in this case, map and totalVal — to calculate the Q2 sales total.

[[Q2data = filter(WholeJSON.yr2021, x -> x.quarter == 'Q2');
totalVal(map(Q2data, x -> x.sales))]]

[[Q2data = filter(WholeJSON['yr2021'], x -> x['quarter'] == 'Q2');
totalVal(map(Q2data, x -> x['sales']))]]

Now suppose you want the combined sales total for Q2 and Q3. This requires a single-field filter that applies two conditions. Specifically, you want all objects for which the quarter value is Q2 or Q3. Here is the ATL:

The result is a filtered array containing Objects 24–72. Again, you could interrogate the filtered array further with other ATL functions to calculate, for example, the total sales value for Q2–Q3.

Note that the ATL uses the OR operator (||) to add an extra condition. The other option is to add a condition with the AND operator (&&). This is less common in single-field filters, but here's one example:

The filter applies two conditions. Since the conditions are separated by the AND operator (&&), the object must meet both conditions to pass the filter. When two conditions are separated by the OR operator (||), the object passes by meeting either condition.

Suppose that you want the average Q2 sales value for Boston. This requires a multiple-field filter.

You can then interrogate the filtered array to calculate the average sales value.

[[Q2Boston = filter(WholeJSON.yr2021, x -> x.city == 'Boston' && x.quarter == 'Q2'); mean(map(Q2Boston, x -> x.sales))]]

The following filter checks data in four fields:

The above ATL retains all objects for which:

All examples above use a lambda expression to define the filter conditions; and in each case, x represents the input object. Use a different symbol for the object if it makes your ATL easier to follow. For example:

[[filter(WholeJSON.yr2021,  inputObject -> inputObject.region == 'South' && inputObject.quarter == 'Q4' && (inputObject.sales > 65000 || percentageChange(inputObject.sales,inputObject.target) > 5))]]

For a case-insensitive filter, you must define a condition that is case-insensitive.

Suppose you want all objects for December (Objects 89–96). Most have have a month value of 'Dec', but several have a different value such as 'DEC' or 'dec'. A filter that is case-sensitive won't return each desired object.

Here is one way to account for the variations in casing:

Now, it doesn't matter if the object's month value is 'dec', 'Dec', 'DEC', or 'DEc'. All pass the filter because the condition converts the value to lowercase before testing if that value is equal to 'dec' (the constant).

Suppose that you want the total sales value for Q2–Q4 (inclusive). First, you need all objects for which the quarter value is not Q1. The simplest way to achieve this is to use the negation operator.

Note that this ATL uses the NOT EQUAL TO operator (!=). This is formed by adding the negation operator (!) before an equals sign. You can then interrogate the filtered array to get the total sales value:

[[notQ1data = filter(WholeJSON.yr2021, object -> object.quarter != 'Q1'); totalVal(map(notQ1data, object -> object.sales))]]

You can also use the negation operator before any ATL function that returns a Boolean value of true or false. The negation operator flips true to false, and vice versa. For example:

The first example retains objects with a month value that starts with the character 'J'. The second does the opposite — that is, it retains objects with a month value that does not start with 'J'.

A common requirement is to filter OUT objects with a null value in a specific field.

You can do this by using the NOT EQUAL TO operator (!=).

For example, to remove all objects with a null target value:

Note that Objects 94–96 have a null target value; therefore, they don't pass the filter.

Sometimes, you must test for nulls before you apply another filter condition. For example, suppose you want all objects with a target value greater than 55,000. You might try a single-condition filter:

Studio returns an error here because when the function evaluates Objects 94–96, it finds a null value in the target field, and it cannot assess a null value against the given constant (55000).

You can avoid this by adding a condition that tests for nulls.

[[filter(WholeJSON.yr2021, x -> x.target != null && x.target > 55000)]]

You can combine filter with contains to apply less stringent filters. Rather than filtering by looking for an exact match, you can filter by checking whether the relevant field value merely contains the given string value.

To understand this better, compare and contrast these examples:

This first example tests for exact matches. It retains all objects for which the region value is 'South'.

This second example tests for objects with a region value that contains the string 'South'. The result is a larger filtered array because it includes objects with a region value of 'South' or 'Southeast'.

A more powerful way to use contains is to test if the field value is one of a range of values.

For example, to retain all objects for April–July:

[[

wantedMonths = makeList('Apr', 'May', 'Jun', 'Jul');

filter(WholeJSON.yr2021, x -> contains(wantedMonths, x.month))

]]

The first line defines a list of wanted values. The second line filters the array by searching the wanted list for each object's month value. If the object's value is found in the list, the object passes the filter.

Use the negation operator (!) before contains to apply a does-not-contain filter.

[[

unwantedMonths = makeList('Apr', 'May', 'Jun', 'Jul');

filter(WholeJSON.yr2021, x -> !contains(unwantedMonths, x.month))

]]

All previous examples show how to filter an array of JSON objects; however, you can also have your data in a two-dimensional array — that is, an array or arrays. Here's a small example dataset:

{
    "Q1data": [
	[1, "Austin", "Jan", 56000, 50000],
	[2, "Boston", "Jan", 66000, 70000],
	[3, "Dallas", "Jan", 62000, 60000],
	[4, "Austin", "Feb", 51000, 45000],
	[5, "Boston", "Feb", 63000, 62000],
	[6, "Dallas", "Feb", 54000, 57000],
	[7, "Austin", "Mar", 52000, 50000],
	[8, "Boston", "Mar", 74000, 70000],
	[9, "Dallas", "Mar", 59000, 60000]
    ]
}

When your JSON data is structured this way, there are no fields — e.g. month or sales — to reference for the purpose of data retrieval. Instead, you must use bracket notation with zero-based indexing.

Note that the second value in each inner array is the name of a city. Zero-based indexing applies, so you can apply a by-city filter by referencing the values at Index 1. For example, to get the arrays for Boston:

To filter by month and sales, reference the values at Indexes 2 and 3.

It might help to clarify your ATL if you create variables for the required index numbers. For example:

[[

month = 2; sales = 3;

filter(WholeJSON.Q1data, x -> x[month] == 'Jan' && x[sales] > 60000)

]] 

Once you have filtered the top-level array, you can interrogate it further with other ATL functions.

To get the Jan–Feb data for Dallas and calculate the sales total, the ATL is:

[[

city = 1; month = 2; sales = 3;

filteredData = filter(WholeJSON.Q1data, x -> x[city] == 'Dallas' && x[month] != 'Mar');

allSales = map(filteredData, x -> x[sales]);

totalSales = currencyFormat(sum(allSales));

joinStrings('Excluding March, the Q1 sales total for Dallas is ', totalSales, '.')

]]

The output text: