N1QL Queries and Results

N1QL Queries and Results

In N1QL, you submit queries using any of the DML statements.

As with SQL, you can have nested sub-queries. N1QL queries run on JSON documents, and you can query over multiple documents by using the JOIN clause.

Because data in N1QL can be nested , there are operators and functions that let you navigate through nested arrays. Because data can be irregular, you can specify conditions in the WHERE clause to retrieve data.

You can use standard GROUP BY, ORDER BY, LIMIT, and OFFSET clauses as well as a rich set of functions to transform the results as needed.

Queries

In N1QL, a query returns a set of documents. The returned document set is not required to be uniform, though it can be. A SELECT statement that specifies a fixed set of attribute (column) names results in a uniform set of documents and a SELECT statement that specifies the wild card (*) results in a non-uniform result set. The only guarantee is that every returned document meets the query criteria.

Here's a sample query and the result returned:

Query:
SELECT name, brewery_id from `beer-sample` WHERE brewery_id IS NOT MISSING LIMIT 2; 
Result:
{
    "requestID": "fbea9e79-a2e2-4ab8-9fdc-14e098838cc1",
    "signature": {
        "brewery_id": "json",
        "name": "json"
    },
    "results": [
        {
            "brewery_id": "big_horn_brewing_the_ram_2",
            "name": "Schaumbergfest"
        },
        {
            "brewery_id": "ballast_point_brewing",
            "name": "Wahoo Wheat Beer"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "131.492184ms",
        "executionTime": "131.261322ms",
        "resultCount": 2,
        "resultSize": 205
    }
}			

Paths

N1QL provides paths to support nested data. Paths use dot notation syntax to identify the logical location of an attribute within a document. For example, to get the street from a customer order, use the path orders.billTo.street. This path refers to the value for street in the billTo object. A path is used with arrays or nested objects to get to attributes within the data structure.

Array syntax in the path can also be used to get to information. For example, the path orders.items[0].productId evaluates to the productId value for the first array element under the order item, items.

Paths provide a method for finding data in document structures without having to retrieve the entire document or handle it within an application. Any document data can be requested and returned to an application. When only relevant information is returned to an application, querying bandwidth is reduced.

See Nested Path Expressions for more details.