Projections

A projection is a value that is returned by a query statement (SELECT, MATCH).

Eg. the following query

SELECT name AS firstName, age * 12 AS ageInMonths, out("Friend")
FROM Person
WHERE surname = 'Smith'

has three projections:

name as firstName
age * 12 as ageInMonths
out("Friend")

Syntax

A projection has the following syntax:

<expression> [<nestedProjection>] [ AS <alias> ]

<expression> is an expression (see SQL Syntax) that represents the way to calculate the value of the single projection
<alias> is the Identifier (see SQL Syntax) representing the field name used to return the value in the result set

A projection block has the following syntax:

[DISTINCT] <projection> [, <projection> ]*

DISTINCT: removes duplicates from the result-set

Query result

By default, a query returns a different result-set based on the projections it has:

no projections: The result set is made of records as they arrive from the target, with the original @rid and @type attributes (if any)
* alone: same behavior as without * except the hidden property attribute is applied.
* plus other projections: records of the original target, merged with the other projection values, with @rid and @type of the original record.
expand(<projection>): The result set is made of the records returned by the projection, expanded (if the projection result is a link or a collection of links) and unwinded (if the projection result is a collection). Nothing in all the other cases.
one or more projections: temporary records (with temporary @rid and no @type). Projections that represent links are returned as simple @rid values, unless differently specified in the fetch plan.

Projection values can be overwritten in the final result, the overwrite happens from left to right.

eg.

SELECT 1 AS a, 2 AS a

will return [{"a":2}]

eg.

Having the record {"@type":"Foo", "name":"bar", "@rid":"#12:0"}

SELECT *, "hey" AS name FROM Foo

will return [{"@type":"Foo", "@rid":"#12:0", "name":"hey"}]

SELECT  "hey" AS name, * FROM Foo

will return [{"@type":"Foo", "@rid":"#12:0", "name":"bar"}]

When saving back a record with a valid rid, you will overwrite the existing record! So pay attention when using * together with other projections.

The result of the query can be further unwound using the UNWIND operator.

expand() cannot be used together with GROUP BY.

Aliases

The alias is the field name that a projection will have in the result-set.

An alias can be implicit, if declared with the AS keyword, eg.

SELECT name + " " + surname AS full_name FROM Person

Result: [{"full_name":"John Smith"}]

An alias can be implicit, when no AS is defined, eg.

SELECT name FROM Person

Result: [{"name":"John"}]

An implicit alias is calculated based on how the projection is written. By default, ArcadeDB uses the plain string representation of the projection as alias.

SELECT 1+2 AS sum

Result: [{"sum": 3}]

SELECT parent.name+" "+parent.surname AS full_name FROM Node

Result: [{"full_name": "John Smith"}]

The string representation of a projection is the exact representation of the projection string, without spaces before and after dots and brackets, no spaces before commands, a single space before and after operators.

eg.

SELECT 1+2

Result: [{"1 + 2": 3}]

Note the space before and after the plus symbol.

SELECT parent.name+" "+parent.surname FROM Node

Result: [{"parent.name + \" \" + parent.nurname": "John Smith"}]

SELECT items[4] from Node

Result: [{"items[4]": "John Smith"}]

Nested projections

Syntax:

":{" ( * | (["!"] <identifier> ["*"] (<comma> ["!"] <identifier> ["*"])* ) ) "}"

A projection can refer to a link or to a collection of links, eg. a LIST or MAP. In some cases you can be interested in the expanded object instead of the RID.

Here is an example to remove the @rid and @type properties from all properties, but not the properties itself:

SELECT *:{!@rid,!@type} FROM doc

Let’s clarify this with another example. This is our dataset:

@rid	name	surname	parent
#12:0	foo	fooz
#12:1	bar	barz	#12:0
#12:2	baz	bazz	#12:1

@rid

name

surname

parent

#12:0

foo

fooz

#12:1

bar

barz

#12:0

#12:2

baz

bazz

#12:1

Given this query:

SELECT name, parent FROM TheType WHERE name = 'baz'

The result is

{
   "name": "baz",
   "parent": #12:1
}

Now suppose you want to expand the link and retrieve some properties of the linked object. You can do it explicitly do it with other projections:

SELECT name, parent.name FROM TheType WHERE name = 'baz'

{
   "name": "baz",
   "parent.name": "bar"
}

but this will force you to list them one by one, and it’s not always possible, especially when you don’t know all their names.

Another alternative is to use nested projections, eg.

SELECT name, parent:{name} FROM TheType WHERE name = 'baz'

{
   "name": "baz",
   "parent": {
      "name": "bar"
   }
}

or with multiple attributes

SELECT name, parent:{name, surname} FROM TheType WHERE name = 'baz'

{
   "name": "baz",
   "parent": {
      "name": "bar"
      "surname": "barz"
   }
}

or using a wildcard

SELECT name, parent:{*} FROM TheType WHERE name = 'baz'

{
   "name": "baz",
   "parent": {
      "name": "bar"
      "surname": "barz"
      "parent": #12:0
   }
}

You can also use the ! exclude syntax to define which attributes you want to exclude from the nested projection:

SELECT name, parent:{!surname} FROM TheType WHERE name = 'baz'

{
   "name": "baz",
   "parent": {
      "name": "bar"
      "parent": #12:0
   }
}

You can also use a wildcard on the right of property names, to specify the inclusion of attributes that start with a prefix, eg.

SELECT name, parent:{surna*} FROM TheType WHERE name = 'baz'

{
   "name": "baz",
   "parent": {
      "surname": "barz"
   }
}

or their exclusion

SELECT name, parent:{!surna*} FROM TheType WHERE name = 'baz'

{
   "name": "baz",
   "parent": {
      "name": "bar",
      "parent": #12:0
   }
}

Nested projection syntax allows for multiple level depth expressions, eg. you can go three levels deep as follows:

SELECT name, parent:{name, surname, parent:{name, surname}} FROM TheType WHERE name = 'baz'

{
   "name": "baz",
   "parent": {
      "name": "bar"
      "surname": "barz"
      "parent": {
         "name": "foo"
         "surname": "fooz"
      }
   }
}

You can also use expressions and aliases in nested projections:

SELECT name, parent.parent:{name, surname} as grandparent FROM TheType WHERE name = 'baz'

{
   "name": "baz",
   "grandparent": {
      "name": "foo"
      "surname": "fooz"
   }
}

Finally, you can rename fields with AS:

SELECT name, parent.parent:{name AS givenname} as grandparent FROM TheType WHERE name = 'baz'

{
   "name": "baz",
   "grandparent": {
      "givenname": "fooz"
   }
}

CASE Expression

Available from version 26.2.1

The CASE expression provides conditional logic similar to if-then-else statements in programming languages. It supports two forms: searched CASE and simple CASE.

Searched CASE Syntax

Evaluates conditions sequentially and returns the result of the first matching condition:

CASE
  WHEN <condition1> THEN <result1>
  WHEN <condition2> THEN <result2>
  [ELSE <default_result>]
END

Simple CASE Syntax

Compares an expression against multiple values:

CASE <expression>
  WHEN <value1> THEN <result1>
  WHEN <value2> THEN <result2>
  [ELSE <default_result>]
END

Behavior

The CASE expression evaluates conditions in order and returns the result of the first matching WHEN clause
If no conditions match and an ELSE clause is provided, the ELSE result is returned
If no conditions match and there is no ELSE clause, null is returned
Multiple WHEN clauses can be specified
Can be used in SELECT projections, WHERE clauses, MATCH statements, LET clauses, and expressions
Supports nested CASE expressions for complex conditional logic
NULL values in conditions follow SQL three-valued logic (NULL comparisons return NULL, which is treated as false)

Examples

Searched CASE with conditions in SELECT:

SELECT
  name,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age >= 18 AND age < 65 THEN 'Adult'
    ELSE 'Senior'
  END as ageGroup
FROM Person

Simple CASE with value matching:

SELECT
  name,
  CASE status
    WHEN 'active' THEN 1
    WHEN 'inactive' THEN 0
    ELSE -1
  END as statusCode
FROM Account

CASE without ELSE (returns null if no match):

SELECT
  name,
  CASE
    WHEN score > 90 THEN 'Excellent'
    WHEN score > 75 THEN 'Good'
  END as grade
FROM Student

CASE in WHERE clause for filtering:

SELECT name, age
FROM Person
WHERE CASE
  WHEN age IS NULL THEN false
  WHEN age < 18 THEN 'minor'
  ELSE 'adult'
END = 'adult'

CASE in MATCH WHERE clause (enum-to-string conversion with pattern matching):

MATCH {type: Product, as: prod,
  where: ((CASE
    WHEN color = 1 THEN 'red'
    WHEN color = 2 THEN 'blue'
    WHEN color = 3 THEN 'green'
  END) ILIKE '%ed%')}
RETURN prod.name

This is particularly useful for converting enum values (stored as integers) to human-readable strings that can be filtered with wildcards.

CASE in MATCH RETURN clause:

MATCH {type: Person, as: p, where: (age IS NOT NULL)}
RETURN p.name,
  CASE
    WHEN p.age < 18 THEN 'minor'
    WHEN p.age < 65 THEN 'adult'
    ELSE 'senior'
  END as category
ORDER BY p.name

Nested CASE expressions:

SELECT
  name,
  CASE
    WHEN balance > 0 THEN
      CASE
        WHEN balance > 10000 THEN 'Premium'
        ELSE 'Standard'
      END
    ELSE 'Overdrawn'
  END as accountStatus
FROM Account

CASE in LET clause:

SELECT $category
FROM Product
LET $category = CASE
  WHEN price < 10 THEN 'Budget'
  WHEN price < 100 THEN 'Standard'
  ELSE 'Premium'
END

CASE with NULL handling:

SELECT
  name,
  CASE
    WHEN age IS NULL THEN 'Unknown'
    WHEN age < 18 THEN 'Minor'
    ELSE 'Adult'
  END as status
FROM Person