Advanced Filtering: Dates, Aggregation & Grouping, and Combining Filters
Published December 15th, 2023
Introduction
Directus is a really powerful data platform that automatically generates REST and GraphQL APIs, and an SDK to make working with them more convenient.
There's a number of powerful query parameters to filter, sort, limit, and apply functions to the data returned. But with any powerful tool, complex usage can require a learning curve.
In this post, we'll cover some common and powerful advanced querying that you can use in your applications. In this example, imagine a blog with a search and some time-based filters. Note that each filter has a number of posts that match the query.
Before diving into queries, set up a new collection called articles with title and published_date fields. Any extra fields are fine, but we won't use them today. Then create 10 example items that we can use to test our queries.
Initialize the Directus SDK
- Create a new directory and open it in both your code editor and a terminal.
- In your terminal, run
npm init -yto create apackage.jsonfile. - Run
npm install @directus/sdkto install the Directus SDK. - Create an
index.jsfile and initialize the SDK, replacing the example URL with your Directus Project URL:
import { createDirectus, rest, aggregate, readItems } from "@directus/sdk";
const client = createDirectus("https://directus.example.com").with(rest());import { createDirectus, rest, aggregate, readItems } from "@directus/sdk";
const client = createDirectus("https://directus.example.com").with(rest());Get Data By Month
- The following filter will return any item in the
articlescollection that is published in months 1, 2, or 3 (January, February, or March). - This filter uses the
_orfilter operator which contains an array. - If at least one of the object conditions are
true, it will be included. month()is a DateTime function that will extract the month from a datetime, date, or timestamp field and return the month number.
client.request(
readItems("articles", {
filter: {
_or: [
{
"month(published_date)": {
// January
_eq: 1,
},
},
{
"month(published_date)": {
// Feb
_eq: 2,
},
},
{
"month(published_date)": {
// March
_eq: 3,
},
},
],
},
})
);client.request(
readItems("articles", {
filter: {
_or: [
{
"month(published_date)": {
// January
_eq: 1,
},
},
{
"month(published_date)": {
// Feb
_eq: 2,
},
},
{
"month(published_date)": {
// March
_eq: 3,
},
},
],
},
})
);Save the file, and run node index.js to try it out and see an array of matching items. Of course, you require at least one item which matches the criteria to receive a non-empty array.
Get Data By Year
The same approach can be used with any other DateTime functions, such as year():
client.request(
readItems("articles", {
filter: {
_or: [
{
"year(published_date)": {
_eq: 2023,
},
},
{
"year(published_date)": {
_eq: 2022,
},
},
],
},
})
);client.request(
readItems("articles", {
filter: {
_or: [
{
"year(published_date)": {
_eq: 2023,
},
},
{
"year(published_date)": {
_eq: 2022,
},
},
],
},
})
);Dynamic Variables
Directus also has a set of dynamic variables that can be used in filtering, including $NOW and $NOW(<adjustment>) (for example $NOW(-1 week)), that can be used for relative filters.
Combining Filters: Date & Search Term
Filter syntax can get confusing as you build increasingly complex requests. However, with what we've learnt so far, we can create something very powerful. This filter starts with an _and logical operator - this means that every member of the array must be true for a match, and there are three:
- Must be published in January, February, or March.
- Must be published in 2022 or 2023.
- The title must contain 'Hello'. Notice that for both of the month-based filters, it begins with an
_orlogical operator. Only one of these must match for the whole item to count for the_andoperator.
client.request(
readItems("articles", {
filter: {
_and: [
{
_or: [
{
"month(published_date)": {
_eq: 1,
},
},
{
"month(published_date)": {
_eq: 2,
},
},
{
"month(published_date)": {
_eq: 3,
},
},
],
},
{
_or: [
{
"year(published_date)": {
_eq: 2022,
},
},
{
"year(published_date)": {
_eq: 2023,
},
},
],
},
{
title: {
_contains: "Hello",
},
},
],
},
})
);client.request(
readItems("articles", {
filter: {
_and: [
{
_or: [
{
"month(published_date)": {
_eq: 1,
},
},
{
"month(published_date)": {
_eq: 2,
},
},
{
"month(published_date)": {
_eq: 3,
},
},
],
},
{
_or: [
{
"year(published_date)": {
_eq: 2022,
},
},
{
"year(published_date)": {
_eq: 2023,
},
},
],
},
{
title: {
_contains: "Hello",
},
},
],
},
})
);Aggregation & Grouping
Aggregation can be used to perform operations on the entire dataset and return the result. Grouping allows aggregations to take place on segments of data.
Count Articles By Year
In this example, we will combine aggregation and grouping. Aggregation allows us to perform a count on all articles that exist. The result will then be displayed for each bucket as grouped by the year value.
const result = await client.request(
aggregate('articles', {
aggregate: { count: '*' },
groupBy: ['year(published_date)']
})
);
// Response
{
"data": [
{
"published_date_year": 2022,
"count": 35
},
{
"published_date_year": 2023,
"count": 44
}
]
}const result = await client.request(
aggregate('articles', {
aggregate: { count: '*' },
groupBy: ['year(published_date)']
})
);
// Response
{
"data": [
{
"published_date_year": 2022,
"count": 35
},
{
"published_date_year": 2023,
"count": 44
}
]
}You can use the month() DateTime function to perform the same logic for the month filters.
Summary
The examples given above use hard-coded values, but with little additional work in your real-world application, you can create dynamic queries that returns just the data your users want. By following this guide based on a real-world example, you have not only learnt how to combine filters, but also been introduced to logical operators, DateTime functions, and explored aggregation & grouping.