FROM Clause

The FROM clause says what a query reads. It names a value — usually a collection, such as a table, an array, or an array nested inside a document — and binds a variable to each of its elements in turn. The rest of the query (WHERE, SELECT, …​) works with those bindings.

To iterate over the fields of a struct instead, see FROM UNPIVOT. For the formal semantics, see "Ranging over Bags and Arrays" and its "Mistyping Cases" in the FROM chapter of the PartiQL specification.

Iterating over a collection

FROM orders AS o binds o to each element of orders. The collection can be a bag (<< …​ >>, an unordered collection such as a table) or an array ([ …​ ]).

Input data
{
  'orders': <<
    { 'id': 1001, 'customer': 'Ana', 'total': 156.00 },
    { 'id': 1002, 'customer': 'Raj', 'total': 498.00 },
    { 'id': 1003, 'customer': 'Chloe', 'total': 9.00 }
  >>
}
Query
SELECT o.id, o.customer
FROM orders AS o
WHERE o.total > 100
Result
<<
  { 'id': 1001, 'customer': 'Ana' },
  { 'id': 1002, 'customer': 'Raj' }
>>

As in SQL, AS is optional: FROM orders o means the same thing.

Getting each element’s position with AT

AT binds a second variable to each element’s position. Arrays are ordered, so the position is the element’s zero-based index:

Input data
{
  'playlist': [
    { 'title': 'Blue in Green', 'artist': 'Miles Davis' },
    { 'title': 'Naima', 'artist': 'John Coltrane' },
    { 'title': 'Peace Piece', 'artist': 'Bill Evans' }
  ]
}
Query
SELECT i AS track_number, t.title
FROM playlist AS t AT i
Result
<<
  { 'track_number': 0, 'title': 'Blue in Green' },
  { 'track_number': 1, 'title': 'Naima' },
  { 'track_number': 2, 'title': 'Peace Piece' }
>>

The elements of a bag have no order, so in permissive mode their position is MISSING. A field whose value is MISSING is left out of a struct, so the position field is left out of each result. In strict mode, AT over a bag is an error.

Input data
{
  'orders': <<
    { 'id': 1001, 'customer': 'Ana' },
    { 'id': 1002, 'customer': 'Raj' }
  >>
}
Query
SELECT o.id, i AS position
FROM orders AS o AT i
Result
<<
  { 'id': 1001 },
  { 'id': 1002 }
>>

Iterating over nested collections

A FROM item can range over a collection inside an earlier item’s element. Here each order is paired with each of its own line items, the PartiQL way of unnesting an array. See Joins for more ways to combine FROM items.

Input data
{
  'orders': <<
    {
      'id': 1001,
      'customer': 'Ana',
      'items': [
        { 'sku': 'KB-200', 'qty': 1, 'price': 129.00 },
        { 'sku': 'CBL-1', 'qty': 3, 'price': 9.00 }
      ]
    },
    {
      'id': 1002,
      'customer': 'Raj',
      'items': [
        { 'sku': 'MON-27', 'qty': 2, 'price': 249.00 }
      ]
    }
  >>
}
Query
SELECT o.id, i.sku, i.qty * i.price AS line_total
FROM orders AS o, o.items AS i
Result
<<
  { 'id': 1001, 'sku': 'KB-200', 'line_total': 129.00 },
  { 'id': 1001, 'sku': 'CBL-1', 'line_total': 27.00 },
  { 'id': 1002, 'sku': 'MON-27', 'line_total': 498.00 }
>>

When the value isn’t a collection

Semi-structured data isn’t always the shape a query expects: a field that is usually an array can hold a single value, or be absent. What happens then depends on the mode the query runs in, and the specification defines both:

  • In permissive mode, PartiQL makes the best of it, as described below.

  • In strict mode (also called type-checking mode), each of these cases is an error and the query fails.

FROM ranges over …​ Permissive mode Strict mode

A scalar, such as 5 or 'outdoor'

Treated as a bag of that one value, << 5 >>

Error

A struct

Treated as a bag of that one struct

Error

MISSING or NULL

Treated as << MISSING >> or << NULL >>

Error

A bag, with AT

The position is MISSING

Error

In this example, tags is an array for one product, a single string for another, and absent for the third. In permissive mode, every product still appears:

Input data
{
  'products': <<
    { 'name': 'Trail shoes', 'tags': ['outdoor', 'running'] },
    { 'name': 'Water bottle', 'tags': 'outdoor' },
    { 'name': 'Gift card' }
  >>
}
Query
SELECT p.name, t AS tag
FROM products AS p, p.tags AS t
Result
<<
  { 'name': 'Trail shoes', 'tag': 'outdoor' },
  { 'name': 'Trail shoes', 'tag': 'running' },
  { 'name': 'Water bottle', 'tag': 'outdoor' },
  { 'name': 'Gift card' }
>>

In strict mode, the same query fails: the water bottle’s tags is not a collection, and for the gift card, p.tags is already an error, because the field is absent.