Week 10 - Cypher Queries

This week's lab focuses on writing and understanding Cypher queries. Let's use the database that we created last week as the example.

1. READ

Write queries that find each of the following:

1. All nodes in the database.

MATCH (n) RETURN n

1. All products in the database.

MATCH (n:Product) RETURN n

1. Up to 5 products in the database.

MATCH (n:Product) RETURN n LIMIT 5

1. All products whose prices are greater than 20. We will talk about toFloat() in Section 2. UPDATE.

MATCH (n:Product)
WHERE toFloat(n.purchasePrice) > 20
RETURN n

1. All sales which contain a product with the brand "Mega".

MATCH (s:Sale)-[:CONTAINS]->(p:Product {brand: "Mega"})
RETURN s

Alternatively, If you don't need to reference the "Product" node itself, you can simplify the query by removing "p".

MATCH (s:Sale)-[:CONTAINS]->(:Product {brand: "Mega"})
RETURN s

1. All shops that have sales of a product with the brand "Mega".

MATCH (s:Shop)<-[:SOLD_IN]-(:Sale)-[:CONTAINS]->(:Product{brand:"Mega"})
RETURN s

1. Same query as 6, but return the path.

MATCH p=(s:Shop)<-[:SOLD_IN]-(:Sale)-[:CONTAINS]->(:Product{brand:"Mega"})
RETURN p

1. In this example dataset, our relationships don't have properties. However, what if we need to query based on a property of a relationship? Let's assume there's a "year" property on the "SOLD_IN" relationship. Here's how you can modify the query to include this relationship property:

MATCH (p:Product {brand: "Mega"})-[:CONTAINS]-(s:Sale)-[:SOLD_IN]-(sh:Shop)
WHERE s.date STARTS WITH '2021'
RETURN DISTINCT sh

If there's a "year" property on the "SOLD_IN" relationship, the query should be:

MATCH (p:Product {brand: "Mega"})-[:CONTAINS]->(s:Sale)-[r:SOLD_IN {year: 2021}]->(sh:Shop)
RETURN DISTINCT sh

The output of the above query should be (no changes, no records) as there is no "year" property on the "SOLD_IN" relationship.

Your turn: What does Query 8 mean?

1. Find the path with a length of 4 from the product with ID "TAB_1" to the product with ID "TSH_9":

MATCH p=(:Product {productID: "TAB_1"})-[*4]-(:Product {productID: "TSH_9"})
RETURN p

For more information on path queries: https://neo4j.com/developer-blog/the-power-of-the-path-1/

NOTE: All the variables used in the queries, such as "s", "n", and "p", are self-defined. You can replace these variable names with any valid variable name of your choice.

2. UPDATE

Sometimes, records such as product price might need updating. Instead of uploading a new CSV file, we can write queries to update the information directly in the database.

1. Updates a product's price to a different price.

Check the current price of the product with ID: "BED_3" and its type:

MATCH (p:Product {productID: "BED_3"})
RETURN p.purchasePrice

Figure 1: checking property types from RETURN clause

Alternatively, you can return the node and hover over the property to see its type:

Figure 2: checking property types from nodes

You can see that the purchasePrice is stored as a string. This is because when we created nodes from a CSV file (see Week9), we did not specify the price as a float, so Neo4j automatically treated every variable as a string.

Two methods to change the type:

Method 1: Changing the type when creating the database from a CSV file, you can use conversion functions such as toInteger(), toFloat(), toString(), toBoolean(). You can learn more about these functions in the Neo4j documentation: Scalar functions

For example:

LOAD CSV WITH HEADERS FROM 'file:///products.csv' AS row
CREATE (d:Product {
    productID: row.`Product`,
    category: row.Category,
    subcategory: row.`Sub category`,
    productSize: row.Size,
    purchasePrice: toFloat(row.`Purchase price`),
    color: row.Color,
    brand:row.Brand
})

In this example, toFloat() is used to convert the purchasePrice property from a string to a float during node creation.

Method 2: Updating the type aftering creating:

MATCH (p:Product)
SET p.purchasePrice = toFloat(p.purchasePrice)

Your turn: compare Figure 1 with Figure 3. What's the difference between their results?

Figure 3: checking property types from RETURN clause (after updated)

Now, let's update the price:

MATCH (p:Product {productID: "BED_3"})
SET p.purchasePrice = 125.0

Your turn:

• Verify whether the price of product "BED_3" has been correctly updated to 125.0.

• Choose a shop and update its size to a different value.

• Update the "unitPrice" property of all Sale nodes to float.

• Without changing the type of "quantity" for sale nodes, update the sale whose "SaleID" is "S000004969" to have a quantity of "5.0".

• Remove the "subcategory" property from all product nodes. https://neo4j.com/docs/cypher-manual/current/clauses/remove/#:~:text=The%20REMOVE%20clause%20is%20used,label%20on%20it%2C%20nothing%20happens.

3. DELETE

1. Deletes a specific shop in the database

MATCH (n:Shop {shopID: "shop_14"})
DETACH DELETE n

1. Deletes a relationship between a specific Shop and a specific Sale

MATCH p=(n:Sale{saleID: "S000001480"})-[r:SOLD_IN]->(s:Shop{shopID:"shop_0"})
DELETE r

Your turn:

1. What is the difference between "DELETE" and "DETACH DELETE": https://neo4j.com/docs/cypher-manual/current/clauses/delete/
2. Delete all nodes and relationships in the database.