Using the BY clause to query document ID

While you can define fields that are intended to be unique identifiers (for example, a vehicle's VIN), the true unique identifier of a document is the id metadata field, as described in Inserting documents. For this reason, you can use the id field to create relationships between tables.

The document id field is directly accessible in the committed view only, but you can also project it in the default user view by using the BY clause. For an example, see the following query and its results.

SELECT r_id, r.VIN, r.LicensePlateNumber, r.State, r.City, r.Owners
FROM VehicleRegistration AS r BY r_id
WHERE r_id = '3Qv67yjXEwB9SjmvkuG6Cp'

{
    r_id: "3Qv67yjXEwB9SjmvkuG6Cp",
    VIN: "1N4AL11D75C109151",
    LicensePlateNumber: "LEWISR261LL",
    State: "WA",
    City: "Seattle",
    Owners: {
        PrimaryOwner: { PersonId: "294jJ3YUoH1IEEm8GSabOs" },
        SecondaryOwners: [{ PersonId: "5Ufgdlnj06gF5CWcOIu64s" }]
    }
}

In this query, r_id is a user-defined alias that is declared in the FROM clause, using the BY keyword. This r_id alias binds to the id metadata field for each document in the query's result set. You can use this alias in the SELECT clause and also in the WHERE clause of a query in the user view.

To access other metadata attributes, however, you must query the committed view.

Joining on document ID

Suppose that you're using the document id of one table as a foreign key in a user-defined field of another table. You can use the BY clause to write an inner join query for the two tables on these fields (similar to Joining the committed and user views in the previous topic).

The following example joins two tables named DriversLicense and Person on their PersonId and document id fields respectively, using the BY clause for the latter.

SELECT * FROM DriversLicense AS d INNER JOIN Person AS p BY pid
ON d.PersonId = pid
WHERE pid = '1CWScY2qHYI9G88C2SjvtH'

To learn how to make changes to a document in your table, proceed to Updating and deleting documents.