Data Retrieval
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.
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.
| SQL | FETCH |
|---|---|
| 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
SQL
FETCH supplier (key, date_created, address, name)
Certainly, here's the provided JSON data reordered and converted into a Markdown table:
| key | date_created | name | address |
|---|---|---|---|
| 1 | 2023-09-22 11:43:49 | Aawesome Albattani | 1234 Elm Street, Suite 567 Springfield, USA |
| 2 | 2023-09-23 16:12:34 | Star Tech Solutions | 4567 Maple Avenue, Unit 890 Willowville, Canadaa |
| 3 | 2023-09-23 16:12:44 | Eco Supply Pro | 789 Oak Lane, Building 123 Birchwood, UK |
| 4 | 2023-09-23 16:12:50 | Summit Source Enterprises | 5678 Pine Road, Apt. 234 Cedarville, Australia |
| 5 | 2023-09-23 16:12:58 | Power Wave Industries | 4321 Birch Drive, Floor 345 Redwood City, New Zealand |
| 6 | 2023-09-23 16:13:04 | Green Earth Suppliers | 9876 Cedar Lane, Room 678 Pinecrest, South Africa |
| 7 | 2023-09-23 16:13:11 | Apex Global Trading | 3456 Redwood Street, Office 901 Oakville, India |
| 8 | 2023-09-23 16:13:17 | Quantum Tech Supplies | 6543 Birch Avenue, Shop 456 Willowbrook, France |
| 9 | 2023-09-23 16:13:24 | SwiftSource Innovations |
The equal operator
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (name == "Aawesome Albattani")
| key | date_created | name | address |
|---|---|---|---|
| 1 | 2023-09-22 11:43:49 | Aawesome Albattani | 1234 Elm Street, Suite 567 Springfield, USA |
The not equal operator
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (name != "Aawesome Albattani")
| key | date_created | name | address |
|---|---|---|---|
| 2 | 2023-09-23 16:12:34 | Star Tech Solutions | 4567 Maple Avenue, Unit 890 Willowville, Canadaa |
| 3 | 2023-09-23 16:12:44 | Eco Supply Pro | 789 Oak Lane, Building 123 Birchwood, UK |
| 4 | 2023-09-23 16:12:50 | Summit Source Enterprises | 5678 Pine Road, Apt. 234 Cedarville, Australia |
| 5 | 2023-09-23 16:12:58 | Power Wave Industries | 4321 Birch Drive, Floor 345 Redwood City, New Zealand |
| 6 | 2023-09-23 16:13:04 | Green Earth Suppliers | 9876 Cedar Lane, Room 678 Pinecrest, South Africa |
| 7 | 2023-09-23 16:13:11 | Apex Global Trading | 3456 Redwood Street, Office 901 Oakville, India |
| 8 | 2023-09-23 16:13:17 | Quantum Tech Supplies | 6543 Birch Avenue, Shop 456 Willowbrook, France |
| 9 | 2023-09-23 16:13:24 | SwiftSource Innovations |
The LIKE operator
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (name LIKE "%Tech%")
| key | date_created | name | address |
|---|---|---|---|
| 2 | 2023-09-23 16:12:34 | Star Tech Solutions | 4567 Maple Avenue, Unit 890 Willowville, Canadaa |
| 8 | 2023-09-23 16:13:17 | Quantum Tech Supplies | 6543 Birch Avenue, Shop 456 Willowbrook, France |
The lesser than operator
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (key < 5)
| key | date_created | name | address |
|---|---|---|---|
| 1 | 2023-09-22 11:43:49 | Aawesome Albattani | 1234 Elm Street, Suite 567 Springfield, USA |
| 2 | 2023-09-23 16:12:34 | Star Tech Solutions | 4567 Maple Avenue, Unit 890 Willowville, Canadaa |
| 3 | 2023-09-23 16:12:44 | Eco Supply Pro | 789 Oak Lane, Building 123 Birchwood, UK |
| 4 | 2023-09-23 16:12:50 | Summit Source Enterprises | 5678 Pine Road, Apt. 234 Cedarville, Australia |
The lesser than or equal to operator
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (key <= 5)
| key | date_created | name | address |
|---|---|---|---|
| 1 | 2023-09-22 11:43:49 | Aawesome Albattani | 1234 Elm Street, Suite 567 Springfield, USA |
| 2 | 2023-09-23 16:12:34 | Star Tech Solutions | 4567 Maple Avenue, Unit 890 Willowville, Canadaa |
| 3 | 2023-09-23 16:12:44 | Eco Supply Pro | 789 Oak Lane, Building 123 Birchwood, UK |
| 4 | 2023-09-23 16:12:50 | Summit Source Enterprises | 5678 Pine Road, Apt. 234 Cedarville, Australia |
| 5 | 2023-09-23 16:12:58 | Power Wave Industries | 4321 Birch Drive, Floor 345 Redwood City, New Zealand |
The greater than operator
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (key > 5)
| key | date_created | name | address |
|---|---|---|---|
| 6 | 2023-09-23 16:13:04 | Green Earth Suppliers | 9876 Cedar Lane, Room 678 Pinecrest, South Africa |
| 7 | 2023-09-23 16:13:11 | Apex Global Trading | 3456 Redwood Street, Office 901 Oakville, India |
| 8 | 2023-09-23 16:13:17 | Quantum Tech Supplies | 6543 Birch Avenue, Shop 456 Willowbrook, France |
| 9 | 2023-09-23 16:13:24 | SwiftSource Innovations |
The greater than or equal to operator
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (key >= 5)
| key | date_created | name | address |
|---|---|---|---|
| 5 | 2023-09-23 16:12:58 | Power Wave Industries | 4321 Birch Drive, Floor 345 Redwood City, New Zealand |
| 6 | 2023-09-23 16:13:04 | Green Earth Suppliers | 9876 Cedar Lane, Room 678 Pinecrest, South Africa |
| 7 | 2023-09-23 16:13:11 | Apex Global Trading | 3456 Redwood Street, Office 901 Oakville, India |
| 8 | 2023-09-23 16:13:17 | Quantum Tech Supplies | 6543 Birch Avenue, Shop 456 Willowbrook, France |
| 9 | 2023-09-23 16:13:24 | SwiftSource Innovations |
The IS NOT NULL condition
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (address IS NOT NULL)
| key | date_created | name | address |
|---|---|---|---|
| 1 | 2023-09-22 11:43:49 | Aawesome Albattani | 1234 Elm Street, Suite 567 Springfield, USA |
| 2 | 2023-09-23 16:12:34 | Star Tech Solutions | 4567 Maple Avenue, Unit 890 Willowville, Canadaa |
| 3 | 2023-09-23 16:12:44 | Eco Supply Pro | 789 Oak Lane, Building 123 Birchwood, UK |
| 4 | 2023-09-23 16:12:50 | Summit Source Enterprises | 5678 Pine Road, Apt. 234 Cedarville, Australia |
| 5 | 2023-09-23 16:12:58 | Power Wave Industries | 4321 Birch Drive, Floor 345 Redwood City, New Zealand |
| 6 | 2023-09-23 16:13:04 | Green Earth Suppliers | 9876 Cedar Lane, Room 678 Pinecrest, South Africa |
| 7 | 2023-09-23 16:13:11 | Apex Global Trading | 3456 Redwood Street, Office 901 Oakville, India |
| 8 | 2023-09-23 16:13:17 | Quantum Tech Supplies | 6543 Birch Avenue, Shop 456 Willowbrook, France |
The IS NULL condition
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (address IS NULL)
| key | date_created | name | address |
|---|---|---|---|
| 9 | 2023-09-23 16:13:24 | SwiftSource Innovations |
The IN condition
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (key IN (1,3,5))
| key | date_created | name | address |
|---|---|---|---|
| 1 | 2023-09-22 11:43:49 | Aawesome Albattani | 1234 Elm Street, Suite 567 Springfield, USA |
| 3 | 2023-09-23 16:12:44 | Eco Supply Pro | 789 Oak Lane, Building 123 Birchwood, UK |
| 5 | 2023-09-23 16:12:58 | Power Wave Industries | 4321 Birch Drive, Floor 345 Redwood City, New Zealand |
The BEFORE NEXT condition
There are 194 between 03.04.2024 and 22.09.2023.
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (date_created BEFORE NEXT -194 DAYS)
| key | date_created | name | address |
|---|---|---|---|
| 1 | 2023-09-22 11:43:49 | Aawesome Albattani | 1234 Elm Street, Suite 567 Springfield, USA |
The AFTER NEXT condition
There are 194 between 03.04.2024 and 22.09.2023.
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (date_created IN NEXT -194 DAYS)
| key | date_created | name | address |
|---|---|---|---|
| 2 | 2023-09-23 16:12:34 | Star Tech Solutions | 4567 Maple Avenue, Unit 890 Willowville, Canadaa |
| 3 | 2023-09-23 16:12:44 | Eco Supply Pro | 789 Oak Lane, Building 123 Birchwood, UK |
| 4 | 2023-09-23 16:12:50 | Summit Source Enterprises | 5678 Pine Road, Apt. 234 Cedarville, Australia |
| 5 | 2023-09-23 16:12:58 | Power Wave Industries | 4321 Birch Drive, Floor 345 Redwood City, New Zealand |
| 6 | 2023-09-23 16:13:04 | Green Earth Suppliers | 9876 Cedar Lane, Room 678 Pinecrest, South Africa |
| 7 | 2023-09-23 16:13:11 | Apex Global Trading | 3456 Redwood Street, Office 901 Oakville, India |
| 8 | 2023-09-23 16:13:17 | Quantum Tech Supplies | 6543 Birch Avenue, Shop 456 Willowbrook, France |
| 9 | 2023-09-23 16:13:24 | SwiftSource Innovations |
The IN NEXT condition
There are 194 between 03.04.2024 and 22.09.2023.
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (date_created IN NEXT -194 DAYS)
| key | date_created | name | address |
|---|---|---|---|
| 1 | 2023-09-22 11:43:49 | Aawesome Albattani | 1234 Elm Street, Suite 567 Springfield, USA |
| 2 | 2023-09-23 16:12:34 | Star Tech Solutions | 4567 Maple Avenue, Unit 890 Willowville, Canadaa |
| 3 | 2023-09-23 16:12:44 | Eco Supply Pro | 789 Oak Lane, Building 123 Birchwood, UK |
| 4 | 2023-09-23 16:12:50 | Summit Source Enterprises | 5678 Pine Road, Apt. 234 Cedarville, Australia |
| 5 | 2023-09-23 16:12:58 | Power Wave Industries | 4321 Birch Drive, Floor 345 Redwood City, New Zealand |
| 6 | 2023-09-23 16:13:04 | Green Earth Suppliers | 9876 Cedar Lane, Room 678 Pinecrest, South Africa |
| 7 | 2023-09-23 16:13:11 | Apex Global Trading | 3456 Redwood Street, Office 901 Oakville, India |
| 8 | 2023-09-23 16:13:17 | Quantum Tech Supplies | 6543 Birch Avenue, Shop 456 Willowbrook, France |
| 9 | 2023-09-23 16:13:24 | SwiftSource Innovations |
The BEFORE LAST condition
There are 194 between 03.04.2024 and 22.09.2023.
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (date_created BEFORE LAST -194 DAYS)
| key | date_created | name | address |
|---|---|---|---|
| 1 | 2023-09-22 11:43:49 | Aawesome Albattani | 1234 Elm Street, Suite 567 Springfield, USA |
| 2 | 2023-09-23 16:12:34 | Star Tech Solutions | 4567 Maple Avenue, Unit 890 Willowville, Canadaa |
| 3 | 2023-09-23 16:12:44 | Eco Supply Pro | 789 Oak Lane, Building 123 Birchwood, UK |
| 4 | 2023-09-23 16:12:50 | Summit Source Enterprises | 5678 Pine Road, Apt. 234 Cedarville, Australia |
| 5 | 2023-09-23 16:12:58 | Power Wave Industries | 4321 Birch Drive, Floor 345 Redwood City, New Zealand |
| 6 | 2023-09-23 16:13:04 | Green Earth Suppliers | 9876 Cedar Lane, Room 678 Pinecrest, South Africa |
| 7 | 2023-09-23 16:13:11 | Apex Global Trading | 3456 Redwood Street, Office 901 Oakville, India |
| 8 | 2023-09-23 16:13:17 | Quantum Tech Supplies | 6543 Birch Avenue, Shop 456 Willowbrook, France |
| 9 | 2023-09-23 16:13:24 | SwiftSource Innovations |
The AFTER LAST condition
There are 194 between 03.04.2024 and 22.09.2023.
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (date_created AFTER LAST -194 DAYS)
| key | date_created | name | address |
|---|
TEXT
No record found
The IN LAST condition
There are 194 between 03.04.2024 and 22.09.2023.
SQL
FETCH supplier (key, date_created, address, name) FILTER AND (date_created IN LAST -194 DAYS)
| key | date_created | name | address |
|---|
TEXT
No record found