0
# N1QL Queries
1

2
SQL++ (N1QL) query execution provides SQL-like query capabilities for JSON documents in Couchbase. Support for complex queries, joins, aggregations, and analytics operations across document collections.
3

4
## Capabilities
5

6
### Basic Query Execution
7

8
Execute N1QL queries with various options and consistency levels.
9

10
```python { .api }
11
class Cluster:
12
    def query(self, statement: str, options: QueryOptions = None) -> QueryResult:
13
        """
14
        Execute N1QL query.
15

16
        Args:
17
            statement (str): N1QL query statement
18
            options (QueryOptions, optional): Query execution options
19

20
        Returns:
21
            QueryResult: Query results iterator
22

23
        Raises:
24
            QueryException: If query execution fails
25
            TimeoutException: If query times out
26
        """
27

28
class QueryOptions:
29
    def __init__(self, timeout: timedelta = None,
30
                 scan_consistency: QueryScanConsistency = None,
31
                 consistent_with: MutationState = None,
32
                 adhoc: bool = True,
33
                 client_context_id: str = None,
34
                 max_parallelism: int = None,
35
                 pipeline_batch: int = None,
36
                 pipeline_cap: int = None,
37
                 scan_cap: int = None,
38
                 scan_wait: timedelta = None,
39
                 readonly: bool = None,
40
                 profile: QueryProfile = None,
41
                 metrics: bool = False,
42
                 raw: Dict[str, Any] = None,
43
                 **kwargs):
44
        """
45
        N1QL query execution options.
46

47
        Args:
48
            timeout (timedelta, optional): Query timeout
49
            scan_consistency (QueryScanConsistency, optional): Consistency level
50
            consistent_with (MutationState, optional): Consistency token
51
            adhoc (bool): Whether query is ad-hoc (default: True)
52
            client_context_id (str, optional): Client context identifier
53
            max_parallelism (int, optional): Maximum query parallelism  
54
            pipeline_batch (int, optional): Pipeline batch size
55
            pipeline_cap (int, optional): Pipeline capacity
56
            scan_cap (int, optional): Scan capacity
57
            scan_wait (timedelta, optional): Scan wait time
58
            readonly (bool, optional): Read-only query flag
59
            profile (QueryProfile, optional): Profiling level
60
            metrics (bool): Include query metrics
61
            raw (Dict[str, Any], optional): Raw query options
62
            **kwargs: Named parameters for parameterized queries
63
        """
64
```
65

66
### Query Results and Metadata
67

68
Access query results and execution metadata.
69

70
```python { .api }
71
class QueryResult:
72
    def __iter__(self) -> Iterator[dict]:
73
        """Iterate over query result rows."""
74

75
    def metadata(self) -> QueryMetaData:
76
        """Get query execution metadata."""
77

78
    def rows(self) -> List[dict]:
79
        """Get all result rows as list."""
80

81
class QueryMetaData:
82
    @property
83
    def request_id(self) -> str:
84
        """Query request identifier."""
85

86
    @property
87
    def client_context_id(self) -> str:
88
        """Client context identifier."""
89

90
    @property
91
    def status(self) -> QueryStatus:
92
        """Query execution status."""
93

94
    @property
95
    def signature(self) -> dict:
96
        """Query result signature."""
97

98
    @property
99
    def profile(self) -> dict:
100
        """Query execution profile (if enabled)."""
101

102
    @property
103
    def metrics(self) -> QueryMetrics:
104
        """Query execution metrics (if enabled)."""
105

106
    @property
107
    def warnings(self) -> List[QueryWarning]:
108
        """Query execution warnings."""
109

110
class QueryMetrics:
111
    @property
112
    def elapsed_time(self) -> timedelta:
113
        """Total query execution time."""
114

115
    @property
116
    def execution_time(self) -> timedelta:
117
        """Query execution time."""
118

119
    @property
120
    def result_count(self) -> int:
121
        """Number of result rows."""
122

123
    @property
124
    def result_size(self) -> int:
125
        """Size of results in bytes."""
126

127
    @property
128
    def mutation_count(self) -> int:
129
        """Number of mutations (for DML queries)."""
130

131
    @property
132
    def sort_count(self) -> int:
133
        """Number of sorts performed."""
134

135
    @property
136
    def error_count(self) -> int:
137
        """Number of errors encountered."""
138

139
    @property
140
    def warning_count(self) -> int:
141
        """Number of warnings generated."""
142

143
class QueryWarning:
144
    @property
145
    def code(self) -> int:
146
        """Warning code."""
147

148
    @property
149
    def message(self) -> str:
150
        """Warning message."""
151
```
152

153
### Parameterized Queries
154

155
Support for prepared statements and parameterized queries for security and performance.
156

157
```python { .api }
158
class QueryOptions:
159
    def named_parameters(self, **params) -> QueryOptions:
160
        """
161
        Set named parameters for query.
162

163
        Args:
164
            **params: Named parameter values
165

166
        Returns:
167
            QueryOptions: Options with parameters set
168
        """
169

170
    def positional_parameters(self, *params) -> QueryOptions:
171
        """
172
        Set positional parameters for query.
173

174
        Args:
175
            *params: Positional parameter values
176

177
        Returns:
178
            QueryOptions: Options with parameters set
179
        """
180
```
181

182
### Consistency Control
183

184
Control query consistency levels for different use cases.
185

186
```python { .api }
187
class QueryScanConsistency:
188
    NOT_BOUNDED = "not_bounded"      # Fastest, may return stale data
189
    REQUEST_PLUS = "request_plus"    # Consistent with mutations
190

191
class MutationState:
192
    def __init__(self, *mutation_tokens):
193
        """
194
        Mutation state for consistency.
195

196
        Args:
197
            *mutation_tokens: Mutation tokens to be consistent with
198
        """
199

200
    def add(self, *mutation_tokens) -> MutationState:
201
        """Add mutation tokens to state."""
202
```
203

204
## Query Types and Patterns
205

206
### Data Retrieval Queries
207

208
```python
209
# Basic SELECT
210
query = "SELECT name, age FROM `travel-sample` WHERE type = 'user' LIMIT 10"
211
result = cluster.query(query)
212

213
# With parameters
214
query = "SELECT * FROM `travel-sample` WHERE type = $type AND age > $min_age"
215
result = cluster.query(query, QueryOptions(type="user", min_age=21))
216

217
# With consistency
218
options = QueryOptions(scan_consistency=QueryScanConsistency.REQUEST_PLUS)
219
result = cluster.query("SELECT * FROM `travel-sample` WHERE id = 'user123'", options)
220
```
221

222
### Data Modification Queries
223

224
```python
225
# INSERT
226
query = "INSERT INTO `travel-sample` (KEY, VALUE) VALUES ('user::456', {'name': 'Alice', 'age': 30})"
227
result = cluster.query(query)
228

229
# UPDATE
230
query = "UPDATE `travel-sample` SET age = age + 1 WHERE type = 'user' AND name = $name"
231
result = cluster.query(query, QueryOptions(name="John"))
232

233
# DELETE
234
query = "DELETE FROM `travel-sample` WHERE type = 'user' AND age < $min_age"
235
result = cluster.query(query, QueryOptions(min_age=18))
236
```
237

238
### Complex Queries
239

240
```python
241
# JOIN operations
242
query = """
243
SELECT u.name, p.title
244
FROM `travel-sample` u
245
JOIN `travel-sample` p ON KEYS u.preferred_posts
246
WHERE u.type = 'user' AND p.type = 'post'
247
"""
248

249
# Aggregation
250
query = """
251
SELECT category, COUNT(*) as count, AVG(rating) as avg_rating
252
FROM `travel-sample`
253
WHERE type = 'product'
254
GROUP BY category
255
HAVING COUNT(*) > 10
256
ORDER BY avg_rating DESC
257
"""
258

259
# Subqueries
260
query = """
261
SELECT name, age
262
FROM `travel-sample`
263
WHERE type = 'user' AND age > (
264
    SELECT AVG(age) FROM `travel-sample` WHERE type = 'user'
265
)
266
"""
267
```
268

269
## Usage Examples
270

271
### Basic Query Execution
272

273
```python
274
from couchbase.cluster import Cluster
275
from couchbase.auth import PasswordAuthenticator
276
from couchbase.options import QueryOptions, QueryScanConsistency
277

278
cluster = Cluster("couchbase://localhost", 
279
                 ClusterOptions(PasswordAuthenticator("user", "pass")))
280

281
# Simple query
282
query = "SELECT name, age FROM `travel-sample` WHERE type = 'user' LIMIT 5"
283
result = cluster.query(query)
284

285
for row in result:
286
    print(f"Name: {row['name']}, Age: {row['age']}")
287

288
# Get metadata
289
metadata = result.metadata()
290
print(f"Query took: {metadata.metrics.elapsed_time}")
291
print(f"Result count: {metadata.metrics.result_count}")
292
```
293

294
### Parameterized Queries
295

296
```python
297
# Named parameters
298
query = "SELECT * FROM `travel-sample` WHERE type = $doc_type AND age BETWEEN $min_age AND $max_age"
299
options = QueryOptions(doc_type="user", min_age=25, max_age=35)
300
result = cluster.query(query, options)
301

302
# Positional parameters
303
query = "SELECT * FROM `travel-sample` WHERE type = ? AND city = ?"
304
options = QueryOptions().positional_parameters("user", "San Francisco")
305
result = cluster.query(query, options)
306
```
307

308
### Prepared Statements
309

310
```python
311
# Use prepared statements for frequently executed queries
312
query = "SELECT * FROM `travel-sample` WHERE type = $type"
313
options = QueryOptions(adhoc=False, type="user")  # adhoc=False enables preparation
314
result = cluster.query(query, options)
315
```
316

317
### Consistency Control
318

319
```python
320
from couchbase.mutation_state import MutationState
321

322
# Perform mutation
323
doc = {"name": "Bob", "age": 28}
324
mutation_result = collection.upsert("user::789", doc)
325

326
# Query with consistency
327
mutation_state = MutationState(mutation_result.mutation_token)
328
options = QueryOptions(consistent_with=mutation_state)
329
query = "SELECT * FROM `travel-sample` WHERE META().id = 'user::789'"
330
result = cluster.query(query, options)
331
```
332

333
### Query Profiling and Metrics
334

335
```python
336
from couchbase.options import QueryProfile
337

338
# Enable profiling and metrics
339
options = QueryOptions(
340
    profile=QueryProfile.TIMINGS,
341
    metrics=True
342
)
343

344
result = cluster.query("SELECT * FROM `travel-sample` LIMIT 100", options)
345
metadata = result.metadata()
346

347
# Access metrics
348
print(f"Execution time: {metadata.metrics.execution_time}")
349
print(f"Result size: {metadata.metrics.result_size} bytes")
350

351
# Access profile
352
if metadata.profile:
353
    print(f"Profile data: {metadata.profile}")
354

355
# Check warnings
356
for warning in metadata.warnings:
357
    print(f"Warning {warning.code}: {warning.message}")
358
```
359

360
### Error Handling
361

362
```python
363
from couchbase.exceptions import QueryException, TimeoutException
364

365
try:
366
    result = cluster.query("SELECT * FROM `nonexistent-bucket`")
367
    for row in result:
368
        print(row)
369
except QueryException as e:
370
    print(f"Query failed: {e}")
371
    if hasattr(e, 'context'):
372
        print(f"Query: {e.context.statement}")
373
        print(f"Error code: {e.context.error_code}")
374
except TimeoutException:
375
    print("Query timed out")
376
```