# Neo4j Tutorial: Skip, Limit, Merge Clause and Aggregate Functions

Introduction

Welcome to our in-depth Neo4j tutorial on some of the most powerful features for data manipulation and analysis in graph databases. In this guide, we'll explore how to control result sets using SKIP and LIMIT, efficiently create or match data with MERGE, and gain valuable insights using aggregate functions. These features are essential for working with real-world graph data at scale.

This tutorial builds on the fundamentals of Neo4j covered in previous guides. We'll explore each concept through practical examples, allowing you to follow along and apply these techniques to your own projects.

Setting Up Our Sample Database

To demonstrate these features, let's set up a sample database representing a social network with users, posts, and interests. We'll create a diverse dataset to showcase the various techniques.

// Create User nodes
CREATE (alice:User {name: "Alice", age: 28, joined: date("2019-03-15")})
CREATE (bob:User {name: "Bob", age: 32, joined: date("2018-11-22")})
CREATE (charlie:User {name: "Charlie", age: 45, joined: date("2020-01-05")})
CREATE (david:User {name: "David", age: 23, joined: date("2021-07-30")})
CREATE (emma:User {name: "Emma", age: 37, joined: date("2017-06-12")})
CREATE (frank:User {name: "Frank", age: 26, joined: date("2020-10-18")})
CREATE (grace:User {name: "Grace", age: 31, joined: date("2019-08-24")})
CREATE (hannah:User {name: "Hannah", age: 29, joined: date("2018-05-11")})
CREATE (ian:User {name: "Ian", age: 42, joined: date("2021-02-09")})
CREATE (julia:User {name: "Julia", age: 35, joined: date("2017-12-03")})

// Create Post nodes
CREATE (post1:Post {title: "Graph Databases", content: "Neo4j is amazing", created: datetime("2021-01-15T13:37:00")})
CREATE (post2:Post {title: "Cypher Tips", content: "Learn how to query effectively", created: datetime("2021-02-20T09:15:00")})
CREATE (post3:Post {title: "Data Modeling", content: "Nodes and relationships", created: datetime("2021-03-10T17:22:00")})
CREATE (post4:Post {title: "Complex Queries", content: "Advanced pattern matching", created: datetime("2021-04-05T11:05:00")})
CREATE (post5:Post {title: "Performance Tuning", content: "Optimizing your database", created: datetime("2021-05-12T14:30:00")})
CREATE (post6:Post {title: "Graph Algorithms", content: "Finding patterns in data", created: datetime("2021-06-08T10:45:00")})
CREATE (post7:Post {title: "Real-world Applications", content: "Case studies", created: datetime("2021-07-15T16:20:00")})
CREATE (post8:Post {title: "Getting Started", content: "First steps with Neo4j", created: datetime("2021-08-23T12:10:00")})

// Create Interest nodes
CREATE (tech:Interest {name: "Technology", category: "Professional"})
CREATE (music:Interest {name: "Music", category: "Hobby"})
CREATE (sports:Interest {name: "Sports", category: "Hobby"})
CREATE (travel:Interest {name: "Travel", category: "Lifestyle"})
CREATE (cooking:Interest {name: "Cooking", category: "Hobby"})
CREATE (reading:Interest {name: "Reading", category: "Hobby"})

// Create relationships between users and posts (CREATED)
MATCH (alice:User {name: "Alice"}), (post1:Post {title: "Graph Databases"})
CREATE (alice)-[:CREATED]->(post1)

MATCH (bob:User {name: "Bob"}), (post2:Post {title: "Cypher Tips"})
CREATE (bob)-[:CREATED]->(post2)

MATCH (charlie:User {name: "Charlie"}), (post3:Post {title: "Data Modeling"})
CREATE (charlie)-[:CREATED]->(post3)

MATCH (david:User {name: "David"}), (post4:Post {title: "Complex Queries"})
CREATE (david)-[:CREATED]->(post4)

MATCH (emma:User {name: "Emma"}), (post5:Post {title: "Performance Tuning"})
CREATE (emma)-[:CREATED]->(post5)

MATCH (alice:User {name: "Alice"}), (post6:Post {title: "Graph Algorithms"})
CREATE (alice)-[:CREATED]->(post6)

MATCH (bob:User {name: "Bob"}), (post7:Post {title: "Real-world Applications"})
CREATE (bob)-[:CREATED]->(post7)

MATCH (charlie:User {name: "Charlie"}), (post8:Post {title: "Getting Started"})
CREATE (charlie)-[:CREATED]->(post8)

// Create relationships between users and posts (LIKED)
MATCH (alice:User {name: "Alice"}), (post2:Post {title: "Cypher Tips"})
CREATE (alice)-[:LIKED {on: date("2021-02-21")}]->(post2)

MATCH (bob:User {name: "Bob"}), (post1:Post {title: "Graph Databases"})
CREATE (bob)-[:LIKED {on: date("2021-01-16")}]->(post1)

MATCH (charlie:User {name: "Charlie"}), (post1:Post {title: "Graph Databases"})
CREATE (charlie)-[:LIKED {on: date("2021-01-17")}]->(post1)

MATCH (david:User {name: "David"}), (post1:Post {title: "Graph Databases"})
CREATE (david)-[:LIKED {on: date("2021-01-18")}]->(post1)

MATCH (emma:User {name: "Emma"}), (post1:Post {title: "Graph Databases"})
CREATE (emma)-[:LIKED {on: date("2021-01-20")}]->(post1)

MATCH (frank:User {name: "Frank"}), (post2:Post {title: "Cypher Tips"})
CREATE (frank)-[:LIKED {on: date("2021-02-22")}]->(post2)

MATCH (grace:User {name: "Grace"}), (post3:Post {title: "Data Modeling"})
CREATE (grace)-[:LIKED {on: date("2021-03-12")}]->(post3)

MATCH (hannah:User {name: "Hannah"}), (post4:Post {title: "Complex Queries"})
CREATE (hannah)-[:LIKED {on: date("2021-04-07")}]->(post4)

MATCH (ian:User {name: "Ian"}), (post5:Post {title: "Performance Tuning"})
CREATE (ian)-[:LIKED {on: date("2021-05-15")}]->(post5)

MATCH (julia:User {name: "Julia"}), (post6:Post {title: "Graph Algorithms"})
CREATE (julia)-[:LIKED {on: date("2021-06-10")}]->(post6)

// Create relationships between users and interests (INTERESTED_IN)
MATCH (alice:User {name: "Alice"}), (tech:Interest {name: "Technology"})
CREATE (alice)-[:INTERESTED_IN {level: "Expert"}]->(tech)

MATCH (alice:User {name: "Alice"}), (music:Interest {name: "Music"})
CREATE (alice)-[:INTERESTED_IN {level: "Intermediate"}]->(music)

MATCH (bob:User {name: "Bob"}), (tech:Interest {name: "Technology"})
CREATE (bob)-[:INTERESTED_IN {level: "Expert"}]->(tech)

MATCH (bob:User {name: "Bob"}), (sports:Interest {name: "Sports"})
CREATE (bob)-[:INTERESTED_IN {level: "Beginner"}]->(sports)

MATCH (charlie:User {name: "Charlie"}), (tech:Interest {name: "Technology"})
CREATE (charlie)-[:INTERESTED_IN {level: "Expert"}]->(tech)

MATCH (charlie:User {name: "Charlie"}), (reading:Interest {name: "Reading"})
CREATE (charlie)-[:INTERESTED_IN {level: "Advanced"}]->(reading)

MATCH (david:User {name: "David"}), (tech:Interest {name: "Technology"})
CREATE (david)-[:INTERESTED_IN {level: "Intermediate"}]->(tech)

MATCH (emma:User {name: "Emma"}), (tech:Interest {name: "Technology"})
CREATE (emma)-[:INTERESTED_IN {level: "Expert"}]->(tech)

MATCH (emma:User {name: "Emma"}), (cooking:Interest {name: "Cooking"})
CREATE (emma)-[:INTERESTED_IN {level: "Advanced"}]->(cooking)

MATCH (frank:User {name: "Frank"}), (sports:Interest {name: "Sports"})
CREATE (frank)-[:INTERESTED_IN {level: "Expert"}]->(sports)

MATCH (grace:User {name: "Grace"}), (travel:Interest {name: "Travel"})
CREATE (grace)-[:INTERESTED_IN {level: "Advanced"}]->(travel)

MATCH (hannah:User {name: "Hannah"}), (music:Interest {name: "Music"})
CREATE (hannah)-[:INTERESTED_IN {level: "Expert"}]->(music)

MATCH (ian:User {name: "Ian"}), (reading:Interest {name: "Reading"})
CREATE (ian)-[:INTERESTED_IN {level: "Intermediate"}]->(reading)

MATCH (julia:User {name: "Julia"}), (cooking:Interest {name: "Cooking"})
CREATE (julia)-[:INTERESTED_IN {level: "Expert"}]->(cooking)

// Create FOLLOWS relationships between users
MATCH (alice:User {name: "Alice"}), (bob:User {name: "Bob"})
CREATE (alice)-[:FOLLOWS {since: date("2020-01-15")}]->(bob)

MATCH (alice:User {name: "Alice"}), (charlie:User {name: "Charlie"})
CREATE (alice)-[:FOLLOWS {since: date("2020-02-10")}]->(charlie)

MATCH (bob:User {name: "Bob"}), (david:User {name: "David"})
CREATE (bob)-[:FOLLOWS {since: date("2021-08-05")}]->(david)

MATCH (charlie:User {name: "Charlie"}), (emma:User {name: "Emma"})
CREATE (charlie)-[:FOLLOWS {since: date("2019-11-20")}]->(emma)

MATCH (david:User {name: "David"}), (alice:User {name: "Alice"})
CREATE (david)-[:FOLLOWS {since: date("2021-09-12")}]->(alice)

MATCH (emma:User {name: "Emma"}), (bob:User {name: "Bob"})
CREATE (emma)-[:FOLLOWS {since: date("2018-07-30")}]->(bob)

MATCH (frank:User {name: "Frank"}), (alice:User {name: "Alice"})
CREATE (frank)-[:FOLLOWS {since: date("2021-01-05")}]->(alice)

MATCH (grace:User {name: "Grace"}), (bob:User {name: "Bob"})
CREATE (grace)-[:FOLLOWS {since: date("2020-05-22")}]->(bob)

MATCH (hannah:User {name: "Hannah"}), (charlie:User {name: "Charlie"})
CREATE (hannah)-[:FOLLOWS {since: date("2019-09-15")}]->(charlie)

MATCH (ian:User {name: "Ian"}), (david:User {name: "David"})
CREATE (ian)-[:FOLLOWS {since: date("2021-08-18")}]->(david)

MATCH (julia:User {name: "Julia"}), (emma:User {name: "Emma"})
CREATE (julia)-[:FOLLOWS {since: date("2018-11-27")}]->(emma)

Now that we have our social network database, let's explore the key features covered in this tutorial.

SKIP and LIMIT: Controlling Result Sets

SKIP and LIMIT are essential clauses for controlling the size and starting point of your query results. They're particularly useful for pagination and performance optimization.

Basic LIMIT Usage

The LIMIT clause restricts the number of records returned by your query:

// Return the first 5 users
MATCH (u:User)
RETURN u.name, u.age
LIMIT 5

This query returns only the first 5 user nodes with their name and age.

Basic SKIP Usage

The SKIP clause allows you to skip a specified number of records:

// Skip the first 5 users and return the rest
MATCH (u:User)
RETURN u.name, u.age
SKIP 5

This query skips the first 5 user nodes and returns the remaining users.

Combining SKIP and LIMIT for Pagination

By combining SKIP and LIMIT, you can implement pagination in your application:

// Return users for the second page (assuming 3 users per page)
MATCH (u:User)
RETURN u.name, u.age
ORDER BY u.name
SKIP 3
LIMIT 3

This query returns users 4-6 when ordered by name (the second page with 3 users per page).

Practical Example: Top Creators with Pagination

// Find top post creators (second page, 2 per page)
MATCH (u:User)-[:CREATED]->(p:Post)
RETURN u.name AS Creator, count(p) AS PostCount
ORDER BY PostCount DESC
SKIP 2
LIMIT 2

MERGE Clause: Creating or Matching Patterns

The MERGE clause is a powerful tool for ensuring data consistency. It attempts to match a pattern and creates it if it doesn't exist, effectively combining MATCH and CREATE with conditional logic.

Basic MERGE Usage

// Create a new user if they don't exist
MERGE (u:User {name: "Kevin"})
RETURN u

This query checks if a User node with name "Kevin" exists. If it does, it returns the existing node; if not, it creates and returns a new node.

MERGE with ON CREATE and ON MATCH

MERGE becomes even more powerful when combined with ON CREATE and ON MATCH, which specify actions to take depending on whether a new pattern was created or an existing one was matched:

// Create or update a user
MERGE (u:User {name: "Kevin"})
ON CREATE SET u.age = 33, u.joined = date()
ON MATCH SET u.lastSeen = date()
RETURN u

This query:

Looks for a User named "Kevin"
If not found, creates the user with age 33 and sets joined to today's date
If found, updates the lastSeen property to today's date

MERGE with Relationships

MERGE can also be used with relationships, but caution is needed:

// Ensure Kevin is interested in Technology
MATCH (u:User {name: "Kevin"}), (i:Interest {name: "Technology"})
MERGE (u)-[r:INTERESTED_IN]->(i)
ON CREATE SET r.level = "Beginner", r.since = date()
RETURN u, r, i

Note that when using MERGE with relationships, it's typically better to MATCH the nodes first, then MERGE the relationship between them.

Practical Example: Ensuring Unique Following Relationships

// Ensure Alice follows David if she doesn't already
MATCH (alice:User {name: "Alice"}), (david:User {name: "David"})
MERGE (alice)-[f:FOLLOWS]->(david)
ON CREATE SET f.since = date()
RETURN alice.name, "now follows", david.name

Aggregate Functions: Analyzing Graph Data

Aggregate functions allow you to perform calculations on groups of records, providing valuable insights from your graph data.

COUNT: Counting Records

The COUNT function is one of the most commonly used aggregate functions:

// Count the total number of users
MATCH (u:User)
RETURN count(u) AS TotalUsers

// Count users by age group
MATCH (u:User)
RETURN 
  CASE
    WHEN u.age < 30 THEN "Under 30"
    WHEN u.age >= 30 AND u.age < 40 THEN "30-39"
    ELSE "40+"
  END AS AgeGroup,
  count(u) AS Count
ORDER BY AgeGroup

MAX, MIN, and AVG: Numerical Aggregations

These functions perform numerical calculations on property values:

// Find the average, minimum, and maximum user age
MATCH (u:User)
RETURN 
  avg(u.age) AS AverageAge,
  min(u.age) AS YoungestAge,
  max(u.age) AS OldestAge

COLLECT: Gathering Values into Collections

The COLLECT function aggregates values into an array:

// Collect all interests for each user
MATCH (u:User)-[:INTERESTED_IN]->(i:Interest)
RETURN u.name AS User, collect(i.name) AS Interests

COUNT, COLLECT, and SIZE Combined

These functions can be combined for more complex analyses:

// Find users with their interest count and list
MATCH (u:User)
OPTIONAL MATCH (u)-[:INTERESTED_IN]->(i:Interest)
RETURN 
  u.name AS User,
  count(i) AS InterestCount,
  collect(i.name) AS Interests
ORDER BY InterestCount DESC

SUM: Adding Numerical Values

// Calculate total likes per post
MATCH (p:Post)<-[l:LIKED]-(u:User)
RETURN p.title AS Post, count(l) AS TotalLikes
ORDER BY TotalLikes DESC

Advanced Applications

Now let's combine these features to solve more complex problems.

Ranking and Pagination

// Find the most popular interests with pagination
MATCH (i:Interest)<-[:INTERESTED_IN]-(u:User)
RETURN 
  i.name AS Interest,
  i.category AS Category,
  count(u) AS Popularity
ORDER BY Popularity DESC
SKIP 1
LIMIT 3

Ensuring Unique Nodes with Relationship Properties

// Ensure a unique like relationship with timestamp
MATCH (u:User {name: "Kevin"}), (p:Post {title: "Cypher Tips"})
MERGE (u)-[l:LIKED]->(p)
ON CREATE SET l.on = date()
RETURN u.name, "liked", p.title, "on", l.on

Finding Active Users with Aggregation

// Find most active users based on posts created and likes given
MATCH (u:User)
OPTIONAL MATCH (u)-[:CREATED]->(p:Post)
OPTIONAL MATCH (u)-[:LIKED]->(liked:Post)
RETURN 
  u.name AS User,
  count(DISTINCT p) AS PostsCreated,
  count(DISTINCT liked) AS PostsLiked,
  count(DISTINCT p) + count(DISTINCT liked) AS ActivityScore
ORDER BY ActivityScore DESC
LIMIT 5

Content Recommendation with MERGE and Aggregation

// Recommend posts to Kevin based on interests shared with post creators
MATCH (kevin:User {name: "Kevin"})-[:INTERESTED_IN]->(i:Interest)<-[:INTERESTED_IN]-(creator:User),
      (creator)-[:CREATED]->(p:Post)
WHERE NOT (kevin)-[:CREATED|LIKED]->(p)
WITH p, count(DISTINCT i) AS SharedInterests, collect(DISTINCT i.name) AS InterestList
ORDER BY SharedInterests DESC
LIMIT 3
MERGE (kevin:User {name: "Kevin"})
RETURN p.title AS RecommendedPost, SharedInterests, InterestList

Best Practices and Optimization Tips

Using SKIP and LIMIT Efficiently

Always use ORDER BY with SKIP and LIMIT to ensure consistent results
For large skips, consider using indexed properties and WHERE clauses instead
Use parameters for SKIP and LIMIT values, especially in applications

// Better approach for deep pagination
MATCH (u:User)
WHERE u.joined > $lastJoinDate OR (u.joined = $lastJoinDate AND u.name > $lastName)
RETURN u.name, u.joined
ORDER BY u.joined, u.name
LIMIT 5

MERGE Best Practices

Be specific with MERGE patterns to avoid unintended consequences
Use MERGE for nodes with unique business keys
For relationships, first MATCH the nodes, then MERGE the relationship
Use ON CREATE and ON MATCH to maintain data integrity

// Good practice: Match nodes first, then merge relationship
MATCH (u1:User {name: "Kevin"}), (u2:User {name: "Alice"})
MERGE (u1)-[f:FOLLOWS]->(u2)
ON CREATE SET f.since = date()

Aggregate Function Tips

Use count(DISTINCT x) to avoid counting duplicates
Combine multiple aggregate functions in a single query for efficiency
Use aliases (AS) to make results more readable
For complex aggregations, consider using WITH to process intermediate results

// Using WITH for complex aggregation
MATCH (u:User)-[:CREATED]->(p:Post)
WITH u, count(p) AS PostCount
WHERE PostCount > 1
MATCH (u)-[:INTERESTED_IN]->(i:Interest)
RETURN u.name AS User, PostCount, collect(i.name) AS Interests
ORDER BY PostCount DESC

Conclusion

In this tutorial, we've explored several powerful Neo4j features that help you control, analyze, and maintain your graph data:

SKIP and LIMIT for controlling result sets and implementing pagination
MERGE for ensuring data consistency and conditional creation
Aggregate functions for analyzing and summarizing graph data

These features are essential tools in any Neo4j developer's toolkit, allowing for more sophisticated queries and applications. By combining them effectively, you can build robust graph-based solutions that scale well and maintain data integrity.