How can we use filter in Apache age for condition?

Question

In postgreSQL, we can use filter such as-

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.

score 2 · Answer 1 · answered Jun 18 '23 at 15:49

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.

It might be worth adding to the answer that you can test for a range of years (as asked in the original question) using something like `WHERE product.year IN [2022,2023]` Also worth noting that you should not need to use `count` to force an aggregation. In Cypher `sum` alone should do that. — Kelvin Lawrence, Jun 19 '23 at 16:25

score 0 · Answer 2 · answered Jun 18 '23 at 13:43

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'.

This answer shows an example of using `WHERE` to do some filtering, but does not show using `sum` to aggregate numerical sums which is a key part of the question. — Kelvin Lawrence, Jun 19 '23 at 16:28

score 0 · Answer 3 · answered Jun 18 '23 at 18:42

0

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.

answered Jun 18 '23 at 18:42

ahmed_131313

142
6

This answer shows an example of using `WHERE` to do some filtering, but does not show using `sum` to aggregate numerical sums which is a key part of the question. – Kelvin Lawrence Jun 19 '23 at 16:28

score 0 · Answer 4 · answered Jun 19 '23 at 11:21

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.

score 0 · Answer 5 · answered Jun 30 '23 at 12:34

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);

score 0 · Answer 6 · answered Jul 20 '23 at 09:07

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

score 0 · Answer 7 · answered Aug 22 '23 at 11:56

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;

How can we use filter in Apache age for condition?

7 Answers7