Smart SQL Queries

SmartStore supports a Smart SQL query language for free-form SELECT statements. Smart SQL queries combine standard SQL SELECT grammar with additional descriptors for referencing soups and soup fields. This approach gives you maximum control and flexibility, including the ability to use joins. Smart SQL supports all standard SQL SELECT constructs.

As of Mobile SDK 9.1, Smart SQL no longer requires index paths for all fields referenced in SELECT or WHERE clauses, except as noted in the following restrictions.

Smart SQL Restrictions

Syntax

Syntax is identical to the standard SQL SELECT specification but with the following adaptations:

Sample Queries

Consider two soups: one named Employees, and another named Departments. The Employees soup contains standard fields such as:

• First name (firstName)
• Last name (lastName)
• Department code (deptCode)
• Employee ID (employeeId)
• Manager ID (managerId)

The Departments soup contains:

• Name (name)
• Department code (deptCode)

Here are some examples of basic Smart SQL queries using these soups:

1select {employees:firstName}, {employees:lastName}
2from {employees} order by {employees:lastName}
3
4select {departments:name}
5from {departments}
6order by {departments:deptCode}

Joins

Smart SQL also allows you to use joins. For example:

1select {departments:name}, {employees:firstName} || ' ' || {employees:lastName} 
2from {employees}, {departments} 
3where {departments:deptCode} = {employees:deptCode} 
4order by {departments:name}, {employees:lastName}

You can even do self-joins:

1select mgr.{employees:lastName}, e.{employees:lastName} 
2from {employees} as mgr, {employees} as e 
3where mgr.{employees:employeeId} = e.{employees:managerId}

1select {soup1:path1} from {soup1}, {soup2}

1select {soup1}.{soup1:path1} from {soup1}, {soup2}

Aggregate Functions

1select {account:name},
2    count({opportunity:name}),
3    sum({opportunity:amount}),
4    avg({opportunity:amount}),
5    {account:id},
6    {opportunity:accountid}
7from {account},
8    {opportunity}
9where {account:id} = {opportunity:accountid}
10group by {account:name}