Filtering

Filtering helps retrieve only the required data. Let’s say we have an entity car  with a property  year_of_manufacturing and we want all the cars newer than 2015.

The filter will be:

FILTER AND (year_of_manufacturing > 2015)

In contrast with SQL in FETCH logical operators are written in prefix notation (or polish notation) and comparison operators are written in infix notation.

Here are some sample on how to write standard SQL Where clauses in FETCH.

SQLFETCH
WHERE (age = 1)FILTER AND (age == 1)
WHERE (age = 1 AND date_created > “2018-01-01”)FILTER AND (age == 1, date_created > “2018-01-01”)
WHERE (age = 1 OR age IS NULL)FILTER OR (age == 1, age IS NULL)
WHERE (age = 1 AND type = 2) OR (name LIKE “abc%”)FILTER OR (AND (age == 1, type == 2), name LIKE “abc%” )

Filtering examples 

FETCH supplier (key, date_created, address, name)

Certainly, here’s the provided JSON data reordered and converted into a Markdown table:

keydate_creatednameaddress
12023-09-22 11:43:49Aawesome Albattani1234 Elm Street, Suite 567 Springfield, USA
22023-09-23 16:12:34Star Tech Solutions4567 Maple Avenue, Unit 890 Willowville, Canadaa
32023-09-23 16:12:44Eco Supply Pro789 Oak Lane, Building 123 Birchwood, UK
42023-09-23 16:12:50Summit Source Enterprises5678 Pine Road, Apt. 234 Cedarville, Australia
52023-09-23 16:12:58Power Wave Industries4321 Birch Drive, Floor 345 Redwood City, New Zealand
62023-09-23 16:13:04Green Earth Suppliers9876 Cedar Lane, Room 678 Pinecrest, South Africa
72023-09-23 16:13:11Apex Global Trading3456 Redwood Street, Office 901 Oakville, India
82023-09-23 16:13:17Quantum Tech Supplies6543 Birch Avenue, Shop 456 Willowbrook, France
92023-09-23 16:13:24SwiftSource Innovations

The equal operator 

FETCH supplier (key, date_created, address, name) FILTER AND (name == "Aawesome Albattani")
keydate_creatednameaddress
12023-09-22 11:43:49Aawesome Albattani1234 Elm Street, Suite 567 Springfield, USA

The not equal operator 

FETCH supplier (key, date_created, address, name) FILTER AND (name != "Aawesome Albattani")
keydate_creatednameaddress
22023-09-23 16:12:34Star Tech Solutions4567 Maple Avenue, Unit 890 Willowville, Canadaa
32023-09-23 16:12:44Eco Supply Pro789 Oak Lane, Building 123 Birchwood, UK
42023-09-23 16:12:50Summit Source Enterprises5678 Pine Road, Apt. 234 Cedarville, Australia
52023-09-23 16:12:58Power Wave Industries4321 Birch Drive, Floor 345 Redwood City, New Zealand
62023-09-23 16:13:04Green Earth Suppliers9876 Cedar Lane, Room 678 Pinecrest, South Africa
72023-09-23 16:13:11Apex Global Trading3456 Redwood Street, Office 901 Oakville, India
82023-09-23 16:13:17Quantum Tech Supplies6543 Birch Avenue, Shop 456 Willowbrook, France
92023-09-23 16:13:24SwiftSource Innovations

The LIKE operator 

FETCH supplier (key, date_created, address, name) FILTER AND (name LIKE "%Tech%")
keydate_creatednameaddress
22023-09-23 16:12:34Star Tech Solutions4567 Maple Avenue, Unit 890 Willowville, Canadaa
82023-09-23 16:13:17Quantum Tech Supplies6543 Birch Avenue, Shop 456 Willowbrook, France

The lesser than operator 

FETCH supplier (key, date_created, address, name) FILTER AND (key < 5)
keydate_creatednameaddress
12023-09-22 11:43:49Aawesome Albattani1234 Elm Street, Suite 567 Springfield, USA
22023-09-23 16:12:34Star Tech Solutions4567 Maple Avenue, Unit 890 Willowville, Canadaa
32023-09-23 16:12:44Eco Supply Pro789 Oak Lane, Building 123 Birchwood, UK
42023-09-23 16:12:50Summit Source Enterprises5678 Pine Road, Apt. 234 Cedarville, Australia

The lesser than or equal to operator 

FETCH supplier (key, date_created, address, name) FILTER AND (key <= 5)
keydate_creatednameaddress
12023-09-22 11:43:49Aawesome Albattani1234 Elm Street, Suite 567 Springfield, USA
22023-09-23 16:12:34Star Tech Solutions4567 Maple Avenue, Unit 890 Willowville, Canadaa
32023-09-23 16:12:44Eco Supply Pro789 Oak Lane, Building 123 Birchwood, UK
42023-09-23 16:12:50Summit Source Enterprises5678 Pine Road, Apt. 234 Cedarville, Australia
52023-09-23 16:12:58Power Wave Industries4321 Birch Drive, Floor 345 Redwood City, New Zealand

The greater than operator 

FETCH supplier (key, date_created, address, name) FILTER AND (key > 5)
keydate_creatednameaddress
62023-09-23 16:13:04Green Earth Suppliers9876 Cedar Lane, Room 678 Pinecrest, South Africa
72023-09-23 16:13:11Apex Global Trading3456 Redwood Street, Office 901 Oakville, India
82023-09-23 16:13:17Quantum Tech Supplies6543 Birch Avenue, Shop 456 Willowbrook, France
92023-09-23 16:13:24SwiftSource Innovations

The greater than or equal to operator 

FETCH supplier (key, date_created, address, name) FILTER AND (key >= 5)
keydate_creatednameaddress
52023-09-23 16:12:58Power Wave Industries4321 Birch Drive, Floor 345 Redwood City, New Zealand
62023-09-23 16:13:04Green Earth Suppliers9876 Cedar Lane, Room 678 Pinecrest, South Africa
72023-09-23 16:13:11Apex Global Trading3456 Redwood Street, Office 901 Oakville, India
82023-09-23 16:13:17Quantum Tech Supplies6543 Birch Avenue, Shop 456 Willowbrook, France
92023-09-23 16:13:24SwiftSource Innovations

The IS NOT NULL condition 

FETCH supplier (key, date_created, address, name) FILTER AND (address IS NOT NULL)
keydate_creatednameaddress
12023-09-22 11:43:49Aawesome Albattani1234 Elm Street, Suite 567 Springfield, USA
22023-09-23 16:12:34Star Tech Solutions4567 Maple Avenue, Unit 890 Willowville, Canadaa
32023-09-23 16:12:44Eco Supply Pro789 Oak Lane, Building 123 Birchwood, UK
42023-09-23 16:12:50Summit Source Enterprises5678 Pine Road, Apt. 234 Cedarville, Australia
52023-09-23 16:12:58Power Wave Industries4321 Birch Drive, Floor 345 Redwood City, New Zealand
62023-09-23 16:13:04Green Earth Suppliers9876 Cedar Lane, Room 678 Pinecrest, South Africa
72023-09-23 16:13:11Apex Global Trading3456 Redwood Street, Office 901 Oakville, India
82023-09-23 16:13:17Quantum Tech Supplies6543 Birch Avenue, Shop 456 Willowbrook, France

The IS NULL condition 

FETCH supplier (key, date_created, address, name) FILTER AND (address IS NULL)
keydate_creatednameaddress
92023-09-23 16:13:24SwiftSource Innovations

The IN condition 

FETCH supplier (key, date_created, address, name) FILTER AND (key IN (1,3,5))
keydate_creatednameaddress
12023-09-22 11:43:49Aawesome Albattani1234 Elm Street, Suite 567 Springfield, USA
32023-09-23 16:12:44Eco Supply Pro789 Oak Lane, Building 123 Birchwood, UK
52023-09-23 16:12:58Power Wave Industries4321 Birch Drive, Floor 345 Redwood City, New Zealand

The BEFORE NEXT condition

There are 194 between 03.04.2024 and 22.09.2023.

FETCH supplier (key, date_created, address, name) FILTER AND (date_created BEFORE NEXT -194 DAYS)
keydate_creatednameaddress
12023-09-22 11:43:49Aawesome Albattani1234 Elm Street, Suite 567 Springfield, USA

The AFTER NEXT condition

There are 194 between 03.04.2024 and 22.09.2023.

FETCH supplier (key, date_created, address, name) FILTER AND (date_created IN NEXT -194 DAYS)
keydate_creatednameaddress
22023-09-23 16:12:34Star Tech Solutions4567 Maple Avenue, Unit 890 Willowville, Canadaa
32023-09-23 16:12:44Eco Supply Pro789 Oak Lane, Building 123 Birchwood, UK
42023-09-23 16:12:50Summit Source Enterprises5678 Pine Road, Apt. 234 Cedarville, Australia
52023-09-23 16:12:58Power Wave Industries4321 Birch Drive, Floor 345 Redwood City, New Zealand
62023-09-23 16:13:04Green Earth Suppliers9876 Cedar Lane, Room 678 Pinecrest, South Africa
72023-09-23 16:13:11Apex Global Trading3456 Redwood Street, Office 901 Oakville, India
82023-09-23 16:13:17Quantum Tech Supplies6543 Birch Avenue, Shop 456 Willowbrook, France
92023-09-23 16:13:24SwiftSource Innovations

The IN NEXT condition

There are 194 between 03.04.2024 and 22.09.2023.

FETCH supplier (key, date_created, address, name) FILTER AND (date_created IN NEXT -194 DAYS)
keydate_creatednameaddress
12023-09-22 11:43:49Aawesome Albattani1234 Elm Street, Suite 567 Springfield, USA
22023-09-23 16:12:34Star Tech Solutions4567 Maple Avenue, Unit 890 Willowville, Canadaa
32023-09-23 16:12:44Eco Supply Pro789 Oak Lane, Building 123 Birchwood, UK
42023-09-23 16:12:50Summit Source Enterprises5678 Pine Road, Apt. 234 Cedarville, Australia
52023-09-23 16:12:58Power Wave Industries4321 Birch Drive, Floor 345 Redwood City, New Zealand
62023-09-23 16:13:04Green Earth Suppliers9876 Cedar Lane, Room 678 Pinecrest, South Africa
72023-09-23 16:13:11Apex Global Trading3456 Redwood Street, Office 901 Oakville, India
82023-09-23 16:13:17Quantum Tech Supplies6543 Birch Avenue, Shop 456 Willowbrook, France
92023-09-23 16:13:24SwiftSource Innovations

The BEFORE LAST condition

There are 194 between 03.04.2024 and 22.09.2023.

FETCH supplier (key, date_created, address, name) FILTER AND (date_created BEFORE LAST -194 DAYS)
keydate_creatednameaddress
12023-09-22 11:43:49Aawesome Albattani1234 Elm Street, Suite 567 Springfield, USA
22023-09-23 16:12:34Star Tech Solutions4567 Maple Avenue, Unit 890 Willowville, Canadaa
32023-09-23 16:12:44Eco Supply Pro789 Oak Lane, Building 123 Birchwood, UK
42023-09-23 16:12:50Summit Source Enterprises5678 Pine Road, Apt. 234 Cedarville, Australia
52023-09-23 16:12:58Power Wave Industries4321 Birch Drive, Floor 345 Redwood City, New Zealand
62023-09-23 16:13:04Green Earth Suppliers9876 Cedar Lane, Room 678 Pinecrest, South Africa
72023-09-23 16:13:11Apex Global Trading3456 Redwood Street, Office 901 Oakville, India
82023-09-23 16:13:17Quantum Tech Supplies6543 Birch Avenue, Shop 456 Willowbrook, France
92023-09-23 16:13:24SwiftSource Innovations

The AFTER LAST condition

There are 194 between 03.04.2024 and 22.09.2023.

FETCH supplier (key, date_created, address, name) FILTER AND (date_created AFTER LAST -194 DAYS)
keydate_creatednameaddress
No record found

The IN LAST condition

There are 194 between 03.04.2024 and 22.09.2023.

FETCH supplier (key, date_created, address, name) FILTER AND (date_created IN LAST -194 DAYS)
keydate_creatednameaddress
No record found
← Retrieving data
Aggregation →