Skip to main content

Coming from SQL

If you worked with a relational database management system (RDBMS) such as MySQL, MariaDB or PostgreSQL, you will be familiar with its query language, a dialect of SQL (Structured Query Language).

C8's query language is called C8QL. There are some similarities between both languages despite the different data models of the database systems. The most notable difference is probably the concept of loops in C8QL, which makes it feel more like a programming language. The loops suits the schema-less model more natural and makes the query language very powerful while remaining easy to read and write.

The C8 Query Language (C8QL) is similar to the Structured Query Language (SQL) in its purpose. Both support reading and modifying collection data, however C8QL does not support data definition operations, such as creating and dropping databases, collections and indexes.

Though some of the keywords overlap, C8QL syntax differs from SQL. For instance, the SQL WHERE and C8QL FILTER clauses are equivalent in that they both define conditions for returning results. But, SQL uses predefined sequence to determine where the WHERE clause must occur in the statement. In C8QL, clauses execute from left to right, so the position of a FILTER clause in the query determines its precedence.

Despite few differences, anyone with an SQL background should have no difficulty in learning C8QL.

Terminology​

Below is a table with the terms of both systems.

SQLC8QL
databasedatabase
tablecollection
rowdocument
columnattribute
table joinscollection joins
primary keyprimary key (_key attribute)
indexindex

INSERT​

The INSERT keyword adds new documents to a collection. It uses the following syntax:

INSERT document
    INTO collection options

Refer to INSERT for more details.

Inserting a single row / document​

SQL:

INSERT INTO users (name, gender)
    VALUES ("John Doe", "m");

C8QL:

INSERT { name: "John Doe", gender: "m" }
   INTO users

Inserting multiple rows / documents​

SQL:

INSERT INTO users (name, gender)
    VALUES ("John Doe", "m"), 
           ("Jane Smith", "f");

C8QL:

FOR user IN [ 
    { name: "John Doe", gender: "m" },
    { name: "Jane Smith", gender: "f"}
   ]
   INSERT user INTO users

Inserting rows / documents from a table / collection​

SQL:

INSERT INTO backup (uid, name, gender)
    SELECT uid, name, gender
    FROM users
    WHERE active = 1;

C8QL:

FOR user IN users
   FILTER user.active == 1
   INSERT user INTO backup

Generating test rows / documents​

SQL:

Use scripts or stored procedures or populate from an existing table.

C8QL:

 FOR i IN 1..1000
    INSERT {
        name: CONCAT("test", i),
        gender: (i % 2 == 0 ? "f" : "m")
    }
    INTO users

UPDATE​

The UPDATE keyword partially modifies documents in a collection. There are two syntaxes available for this operation:

UPDATE document IN collection options

UPDATE keyExpression WITH document IN collection options

Refer to UPDATE for more details.

Updating a single row / document​

SQL:

UPDATE users 
  SET name = "John Smith"
  WHERE id = 1;

C8QL:

UPDATE { _key: "1" }
  WITH { name: "John Smith" }
  IN users

Adding a new column / attribute with a default value​

SQL:

ALTER TABLE users 
  ADD COLUMN numberOfLogins 
  INTEGER NOT NULL default 0;

C8QL:

FOR user IN users
  UPDATE user 
    WITH { numberOfLogins: 0 } IN users

Adding a new column / attribute with a calculated value​

SQL:

ALTER TABLE users 
  ADD COLUMN numberOfLogins INTEGER 
             NOT NULL default 0;
UPDATE users 
  SET numberOfLogins = (
    SELECT COUNT(*) FROM logins 
    WHERE user = users.id
  ) 
  WHERE active = 1;

C8QL:

FOR user IN users
  FILTER user.active == 1
    UPDATE user 
      WITH { 
        numberOfLogins: LENGTH(
          FOR login IN logins 
            FILTER login.user == user._key 
            COLLECT WITH COUNT INTO numLogins 
            RETURN numLogins 
        )
      } IN users

Adding optional columns / attributes​

SQL:

ALTER TABLE users 
  ADD COLUMN isImportantUser 
             INTEGER default NULL,
  ADD COLUMN dateBecameImportant 
             INTEGER default NULL;
 
UPDATE users 
  SET isImportantUser = 1, 
      dateBecameImportant = UNIX_TIMESTAMP()
  WHERE isImportantUser IS NULL AND (
    SELECT COUNT(*) FROM logins 
      WHERE user = user.id
  ) > 50;
danger

Not directly possible, must set column to default value (e.g. NULL) for rows that do not qualify.

C8QL:

LET date = DATE_NOW()
  FOR user IN users
    FILTER user.isImportantUser == null
    LET numberOfLogins = (
      FOR login IN logins 
        FILTER login.user == user._key
        COLLECT WITH COUNT INTO numLogins
        RETURN numLogins
      )
    FILTER numberOfLogins > 50
    UPDATE user 
      WITH { 
        isImportantUser: 1, 
        dateBecameImportant: date 
      } 
      IN users

Removing a column / attribute​

SQL:

ALTER TABLE users
  DROP COLUMN numberOfLogins;

C8QL:

FOR user IN users
  UPDATE user WITH { numberOfLogins: null } 
    IN users 
  OPTIONS { keepNull: false }

Removing a column / attribute only for some rows / documents​

SQL: *

UPDATE users 
  SET isImportantUser = NULL, 
    dateBecameImportant = NULL
  WHERE isImportantUser = 1 AND active = 0;
danger

Not directly possible, must set column to default value (e.g. NULL) for rows that qualify.

C8QL:

FOR user IN users
  FILTER user.isImportantUser == 1 AND 
         user.active == 0
    UPDATE user 
      WITH { 
        isImportantUser: null, 
        dateBecameImportant: null 
      } 
      IN users 
    OPTIONS { keepNull: false }

REPLACE​

The REPLACE keyword completely modifies documents in a collection. There are two syntaxes available for this operation:

    REPLACE document IN collection options

    REPLACE keyExpression WITH document IN collection options

Refer to REPLACE for more details.

Replacing a single row / document​

SQL:

REPLACE INTO users (name, gender) 
  VALUES ("Jane Smith", "f")
  WHERE id = 1;

C8QL:

REPLACE { _key: "1" }
  WITH {
    name: "Jane Smith",
    gender: "f"
  }
  IN users

Replacing multiple rows / documents in a table​

SQL:

REPLACE INTO users (name, gender)
  SELECT name, gender FROM backup

C8QL:

FOR user IN backup
  REPLACE user 
    WITH { 
      name: backup.name, 
      gender: backup.gender 
    }
    IN users

DELETE / REMOVE​

SQL uses DELETE statements to remove rows from a table. In C8QL, the REMOVE keyword allows you to remove documents from a collection.

Refer to REMOVE for more details.

Deleting a single row / document​

SQL:

DELETE FROM users
  WHERE id = 1;

C8QL:

REMOVE { _key:"1" } 
  IN users

Deleting multiple rows / documents​

SQL:

DELETE FROM users
  WHERE active = 1;

C8QL:

FOR user IN users
  FILTER user.active == 1
  REMOVE user IN users

QUERIES​

When you want to retrieve rows from a table in SQL, you query the database with a SELECT statement. In C8QL, you query documents from a collection using the FOR and RETURN keywords.

Here, FOR iterates over documents in a collection. RETURN determines what the query returns to the client.

Refer to FOR for more details.

Selecting all rows / documents from a table / collection, with all columns / attributes​

SQL:

SELECT * 
  FROM users;

C8QL:

FOR user IN users
  RETURN user

Filtering rows / documents from a table / collection, with projection​

SQL:

SELECT CONCAT(firstName, " ", lastName) 
  AS name, gender FROM users
  WHERE active = 1;

C8QL:

FOR user IN users
  FILTER user.active == 1
  RETURN {
    name: CONCAT(user.firstName, " ",
                 user.lastName),
    gender: user.gender
  }

Sorting rows / documents from a table / collection​

SQL:

SELECT * FROM users
  WHERE active = 1
  ORDER BY name, gender;

C8QL:

FOR user IN users
  FILTER user.active == 1
  SORT user.name, user.gender
  RETURN user

AGGREGATION​

There are a series of functions and clauses in both SQL and C8QL to group or further refine the result-set to get the information you need. For instance, counting documents, finding the smallest or largest value, and so on.

Refer to COLLECT for more details.

Counting rows / documents in a table / collection​

Both SQL and C8QL can count the rows or documents in the result-set and tell you how many it finds. C8QL manages counts using the WITH keyword to count the documents into a return variable.

SQL:

SELECT gender, COUNT(*) AS number FROM users
  WHERE active = 1
  GROUP BY gender;

C8QL:

FOR user IN users
  FILTER user.active == 1
  COLLECT gender = user.gender 
    WITH COUNT INTO number
  RETURN { 
    gender: gender, 
    number: number 
  }

Grouping rows / documents in a table / collection​

In SQL, the GROUP BY clauses collects the result-set according to the given column. C8QL replaces this with the COLLECT keyword.

SQL:

SELECT YEAR(dateRegister) AS year, 
       MONTH(dateRegister) AS month, 
       COUNT(*) AS number 
  FROM users
  WHERE active = 1
  GROUP BY year, month
  HAVING number > 20;

C8QL:

FOR user IN users
  FILTER user.active == 1
  COLLECT
    year = DATE_YEAR(user.dateRegistered), 
    month = DATE_MONTH(user.dateRegistered) 
    WITH COUNT INTO number
    FILTER number > 20
    RETURN { 
      year: year, 
      month: month, 
      number: number 
    }

Minimum, maximum calculation of rows / documents in a table / collection​

Both SQL and C8QL use functions to find the minimum and maximum values for a given field. In C8QL, it’s handled with the COLLECT keyword.

SQL:

SELECT MIN(dateRegistered) AS minDate, 
  MAX(dateRegistered) AS maxDate 
  FROM users
    WHERE active = 1;

C8QL:

FOR user IN users
  FILTER user.active == 1
  COLLECT AGGREGATE
    minDate = MIN(user.dateRegistered),
    maxDate = MAX(user.dateRegistered)
  RETURN { minDate, maxDate }

Building horizontal lists​

SQL:

SELECT gender, GROUP_CONCAT(id) AS userIds 
  FROM users
    WHERE active = 1
    GROUP BY gender;
danger

Not really applicable – use either a concatenated string column or a special datatype (non-portable).

C8QL:

FOR user IN users
  FILTER user.active == 1
  COLLECT gender = user.gender 
    INTO usersByGender
  RETURN { 
    gender: gender, 
    userIds: usersByGender[*].user._key
  }

JOINS​

Similar to joins in relational databases, C8QL has its own implementation of JOINS. Coming from an SQL background, you might find the C8QL syntax very different from your expectations.

Inner join​

SQL:

SELECT * FROM users
  INNER JOIN friends 
  ON (friends.user = users.id);

C8QL:

An inner join can be expressed easily in C8QL by nesting FOR loops and using FILTER statements:

FOR user IN users 
  FOR friend IN friends
    FILTER friend.user == user._key
    RETURN MERGE(user, friend)
danger

In C8QL the preferred way is to return the document parts from the different collections in individual sub-attributes to avoid attribute name conflicts.

Example:

FOR user IN users 
  FOR friend IN friends
    FILTER friend.user == user._key
    RETURN { user: user, friend: friend }

It is also possible to return the matching documents in a horizontal list:

FOR user IN users 
  RETURN { 
    user: user, 
    friends: (
      FOR friend IN friends
        FILTER friend.user == user._key
        RETURN friend
    )
  }

Outer join​

SQL:

SELECT * FROM users
  LEFT JOIN friends 
    ON (friends.user = users.id);

C8QL: Outer joins are not directly supported in C8QL, but can be implemented using subqueries:

FOR user IN users 
  LET friends = (
    FOR friend IN friends
      FILTER friend.user == user._key
      RETURN friend
  )
  FOR friendToJoin IN (
    LENGTH(friends) > 0 ? friends :
      [ { /* no match exists */ } ]
    )
    RETURN { 
      user: user,
      friend: friend
    }

In the main, C8QL is a declarative language. Queries express what results you want but not how you want to get there. C8QL aims to be human-readable, therefore uses keywords from the English language.

It also aims to be client independent, meaning that the language and syntax are the same for all clients, no matter what programming language the clients use. Additionally, it supports complex query patterns and the various data models Macrometa offers.

C8QL also supports several aggregation and string functions. For more information, see C8QL Functions.

How do browse vectors translate into document queries?​

In traditional SQL you may either fetch all columns of a table row by row, using SELECT * FROM table, or select a subset of the columns. The list of table columns to fetch is commonly called column list or browse vector:

SELECT columnA, columnB, columnZ FROM table

Since documents aren't two-dimensional, and neither do you want to be limited to returning two-dimensional lists, the requirements for a query language are higher.

C8QL is thus a little bit more complex than plain SQL at first, but offers much more flexibility in the long run. It lets you handle arbitrarily structured documents in convenient ways, mostly leaned on the syntax used in JavaScript.

Composing the documents to be returned​

The C8QL RETURN statement returns one item per document it is handed. You can return the whole document, or just parts of it.

Given that oneDocument is a document (retrieved like LET oneDocument = DOCUMENT("myusers/3456789") for instance), it can be returned as-is like this:

RETURN oneDocument

The above statement returns a document like:

[
    {
        "_id": "myusers/3456789",
        "_key": "3456789"
        "_rev": "14253647",
        "firstName": "John",
        "lastName": "Doe",
        "address": {
            "city": "Gotham",
            "street": "Road To Nowhere 1"
        },
        "hobbies": [
            { name: "swimming", howFavorite: 10 },
            { name: "biking", howFavorite: 6 },
            { name: "programming", howFavorite: 4 }
        ]
    }
]

To return the hobbies sub-structure only:

RETURN oneDocument.hobbies
[
    [
        { name: "swimming", howFavorite: 10 },
        { name: "biking", howFavorite: 6 },
        { name: "programming", howFavorite: 4 }
    ]
]

To return the hobbies and the address:

RETURN {
    hobbies: oneDocument.hobbies,
    address: oneDocument.address
}
[
    {
        hobbies: [
            { name: "swimming", howFavorite: 10 },
            { name: "biking", howFavorite: 6 },
            { name: "programming", howFavorite: 4 }
        ],
        address: {
            "city": "Gotham",
            "street": "Road To Nowhere 1"
        }
    }
]

To return the first hobby only:

RETURN oneDocument.hobbies[0].name
[
    "swimming"
]

To return a list of all hobby strings:

RETURN { hobbies: oneDocument.hobbies[*].name }
[
    { hobbies: ["swimming", "biking", "programming"] }
]

More complex array and object manipulations can be done using C8QL functions and operators.