How can we use filter in Apache age for condition?

In postgreSQL, we can use filter such as-

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-html -->

SELECT column_name,
       SUM(sales) FILTER (WHERE year = 2022) AS total_sales_2022,
       SUM(sales) FILTER (WHERE year = 2023) AS total_sales_2023
FROM sales_table
GROUP BY column_name;

How can we implement this in Apache age? Anyone help me plz.

As per the Doc,
> To calculate aggregated data, Cypher offers aggregation, analogous to SQL’s GROUP BY.

An equivalent cypher query would be:

SELECT * FROM cypher('sales', $$
MATCH (product) WHERE product.year = 2022 WITH product.name as name, count(*) as c, SUM(product.sales) as 
sales
RETURN name, sales
$$) AS (name agtype, sales agtype);

This query will match for the products, filter on the year and apply aggregation using COUNT (analogous to GROUP BY). Then you can apply SUM aggregation function to calculate the total sales for each product.

In apacheAGE we are not dealing with tables, but with nodes and edges.
Those nodes/edges hold information though their labels and/or properties. For example :

SELECT * FROM cypher('test_graph', $$
CREATE (u:Person {name: 'John'})
CREATE (v:Person {name: 'Jake'})
CREATE (k:Person {name: 'Alice'})
CREATE (l:Person {name: 'Anna'}) $$)
AS (u agtype);

Here we create 4 different nodes with Person as the label, and their respective name as property. If we want to find a node through its label we can do:

SELECT * FROM cypher ('test_graph', $$
MATCH (u:Person)
RETURN u $$)
as (u agtype);

This will return all the nodes in our graph that belong to the Person label. Similarly we can filter through a property of a node for example:

SELECT * FROM cypher ('test_graph', $$
MATCH (u:Person) 
WHERE u.name = 'John'
RETURN u $$)
as (u agtype);

Here this will return only the nodes that have the property name and it equals 'John'.

Let's take an example, assume you want to return all users except for those name are hossam, then you can do that by the following query:

testdb=# SELECT * FROM ag_catalog.cypher('test', $$ MATCH(n: User) WHERE n.name <> 'hossam' RETURN n$$) as (users agtype) ;

this will return all users whose name not equal to hossam. You can notice that we applied a filter using the WHERE clause which is used for that purpose.

I hope this give you a sense of how you can use filters in age.

Once you've created your graph along with the required nodes and properties, your postgresql query can be executed this way in Apache AGE

SELECT *
FROM cypher('your_graph', $$
MATCH (u {year: 2022})
WITH sum(u.sales) as total_sales_2022
ORDER BY u.column_name
RETURN u.column_name, total_sales_2022
$$) AS (column_name agtype, total_sales agtype);

You can visit the AGE docs to learn more about the WITH, ORDER BY and RETURN clauses in AGE.

select *from cypher('sale',$$ MATCH (n:Sales)
WHERE n.year = 2022 OR n.year = 2023
RETURN n.column_name AS column_name,
       SUM(CASE WHEN n.year = 2022 THEN n.sales ELSE 0 END) AS total_sales_2022,
       SUM(CASE WHEN n.year = 2023 THEN n.sales ELSE 0 END) AS total_sales_2023
GROUP BY n.column_name);

SELECT column_name,
       SUM(CASE WHEN year = 2022 THEN sales ELSE 0 END) AS total_sales_2022,
       SUM(CASE WHEN year = 2023 THEN sales ELSE 0 END) AS total_sales_2023
FROM sales_table
GROUP BY column_name;

In the above query, the CASE statement acts as a replacement for the FILTER clause

We can use the FILTER clause in combination with the aggregate function to achieve the desired results:

SELECT column_name,
       SUM(sales) FILTER (WHERE year = 2022) AS total_sales_2022,
       SUM(sales) FILTER (WHERE year = 2023) AS total_sales_2023
FROM sales_table
GROUP BY column_name;