Smart SQL Queries

To exert full control over your queries—or to reuse existing SQL queries—you can define custom SmartStore 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.

  • For soups that use the deprecated external storage feature, Smart SQL still requires index paths for any fields referenced in SELECT or WHERE clauses.

    External storage is deprecated in Mobile SDK 10.0 and will be removed in Mobile SDK 11.0. See Using External Storage for Large Soup Elements.

  • You can’t write MATCH queries with Smart SQL. For example, the following query doesn’t work: SELECT {soupName:_soup} FROM {soupName} WHERE {soupName:name} MATCH 'cat'

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

UsageSyntax
To specify a column{<soupName>:<path>}
To specify a table{<soupName>}
To refer to the entire soup entry JSON string{<soupName>:_soup}
To refer to the internal soup entry ID{<soupName>:_soupEntryId}
To refer to the last modified date{<soupName>:_soupLastModifiedDate}

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:

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

You can even do self-joins:

Doing a join on a JSON1 index requires a slightly extended syntax. For example, instead of using this syntax:

## incorrect syntax
select {soup1:path1} from {soup1}, {soup2}

use this syntax:

## correct syntax
select {soup1}.{soup1:path1} from {soup1}, {soup2}

Smart SQL supports the use of aggregate functions such as:

  • COUNT
  • SUM
  • AVG

For example: