0
# Simple Oracle Document Access (SODA)
1

2
Simple Oracle Document Access (SODA) provides a NoSQL-style API for working with JSON documents in Oracle Database. SODA allows applications to create, read, update, and delete JSON documents stored in Oracle collections without writing SQL. The API provides document operations, filtering, indexing, and metadata management for JSON document storage.
3

4
## Capabilities
5

6
### SODA Database
7

8
The main entry point for SODA operations, accessible through a database connection.
9

10
```python { .api }
11
# Access SODA database through connection
12
def getSodaDatabase(self) -> SodaDatabase: ...
13

14
class SodaDatabase:
15
    def createCollection(self, name: str, metadata: Union[str, dict] = None, mapMode: bool = False) -> SodaCollection:
16
        """
17
        Creates a SODA collection with the given name and returns a new SODA
18
        collection object. If you try to create a collection, and a collection
19
        with the same name and metadata already exists, then that existing
20
        collection is opened without error.
21

22
        Parameters:
23
        - name (str): Name of the collection to create
24
        - metadata (Union[str, dict]): JSON metadata specifying collection configuration
25
        - mapMode (bool): If True, map to existing table instead of creating new table
26

27
        Returns:
28
        SodaCollection: New or existing collection object
29
        """
30

31
    def createDocument(self, content: Any, key: str = None, mediaType: str = "application/json") -> SodaDocument:
32
        """
33
        Creates a SODA document usable for SODA write operations. You only need
34
        to use this method if your collection requires client-assigned keys or
35
        has non-JSON content.
36

37
        Parameters:
38
        - content (Any): Document content (dict, list, str, or bytes)
39
        - key (str): Client-assigned key (if required by collection)
40
        - mediaType (str): MIME type for non-JSON documents
41

42
        Returns:
43
        SodaDocument: New document object
44
        """
45

46
    def getCollectionNames(self, startName: str = None, limit: int = 0) -> List[str]:
47
        """
48
        Returns a list of the names of collections in the database that match
49
        the criteria, in alphabetical order.
50

51
        Parameters:
52
        - startName (str): Starting collection name for filtering
53
        - limit (int): Maximum number of names to return (0 for no limit)
54

55
        Returns:
56
        List[str]: Collection names in alphabetical order
57
        """
58

59
    def openCollection(self, name: str) -> SodaCollection:
60
        """
61
        Opens an existing collection with the given name and returns a new SODA
62
        collection object. If a collection with that name does not exist, None
63
        is returned.
64

65
        Parameters:
66
        - name (str): Name of the collection to open
67

68
        Returns:
69
        SodaCollection: Collection object or None if not found
70
        """
71
```
72

73
### SODA Collection
74

75
Represents a collection of JSON documents with methods for CRUD operations, indexing, and metadata access.
76

77
```python { .api }
78
class SodaCollection:
79
    # Properties
80
    name: str  # Read-only collection name
81
    metadata: dict  # Read-only collection metadata
82

83
    def createIndex(self, spec: Union[dict, str]) -> None:
84
        """
85
        Creates an index on a SODA collection. The spec is expected to be a
86
        dictionary or a JSON-encoded string.
87

88
        Parameters:
89
        - spec (Union[dict, str]): Index specification as dict or JSON string
90
        """
91

92
    def drop(self) -> bool:
93
        """
94
        Drops the collection from the database, if it exists. Note that if the
95
        collection was created with mapMode set to True the underlying table
96
        will not be dropped.
97

98
        Returns:
99
        bool: True if collection was dropped, False if it didn't exist
100
        """
101

102
    def dropIndex(self, name: str, force: bool = False) -> bool:
103
        """
104
        Drops the index with the specified name, if it exists.
105

106
        Parameters:
107
        - name (str): Name of the index to drop
108
        - force (bool): Force dropping of spatial/search indexes
109

110
        Returns:
111
        bool: True if index was dropped, False if it didn't exist
112
        """
113

114
    def find(self) -> SodaOperation:
115
        """
116
        This method is used to begin an operation that will act upon documents
117
        in the collection. It creates and returns a SodaOperation object which
118
        is used to specify the criteria and the operation that will be
119
        performed on the documents that match that criteria.
120

121
        Returns:
122
        SodaOperation: Operation builder for chaining criteria
123
        """
124

125
    def getDataGuide(self) -> SodaDocument:
126
        """
127
        Returns a SODA document object containing property names, data types
128
        and lengths inferred from the JSON documents in the collection. It can
129
        be useful for exploring the schema of a collection.
130

131
        Returns:
132
        SodaDocument: Data guide document or None if no documents exist
133
        """
134

135
    def insertMany(self, docs: list) -> None:
136
        """
137
        Inserts a list of documents into the collection at one time. Each of
138
        the input documents can be a dictionary or list or an existing SODA
139
        document object.
140

141
        Parameters:
142
        - docs (list): List of documents to insert
143
        """
144

145
    def insertManyAndGet(self, docs: list, hint: str = None) -> list:
146
        """
147
        Similarly to insertMany() this method inserts a list of documents into
148
        the collection at one time. The only difference is that it returns a
149
        list of SODA Document objects.
150

151
        Parameters:
152
        - docs (list): List of documents to insert
153
        - hint (str): SQL hint for database processing
154

155
        Returns:
156
        list: List of inserted SodaDocument objects
157
        """
158

159
    def insertOne(self, doc: Any) -> None:
160
        """
161
        Inserts a given document into the collection. The input document can be
162
        a dictionary or list or an existing SODA document object.
163

164
        Parameters:
165
        - doc (Any): Document to insert
166
        """
167

168
    def insertOneAndGet(self, doc: Any, hint: str = None) -> SodaDocument:
169
        """
170
        Similarly to insertOne() this method inserts a given document into the
171
        collection. The only difference is that it returns a SODA Document
172
        object.
173

174
        Parameters:
175
        - doc (Any): Document to insert
176
        - hint (str): SQL hint for database processing
177

178
        Returns:
179
        SodaDocument: Inserted document object
180
        """
181

182
    def listIndexes(self) -> list:
183
        """
184
        Return a list of indexes associated with the collection.
185

186
        Returns:
187
        list: List of index specifications as dictionaries
188
        """
189

190
    def save(self, doc: Any) -> None:
191
        """
192
        Saves a document into the collection. This method is equivalent to
193
        insertOne() except that if client-assigned keys are used, and the
194
        document with the specified key already exists in the collection, it
195
        will be replaced with the input document.
196

197
        Parameters:
198
        - doc (Any): Document to save
199
        """
200

201
    def saveAndGet(self, doc: Any, hint: str = None) -> SodaDocument:
202
        """
203
        Saves a document into the collection. This method is equivalent to
204
        insertOneAndGet() except that if client-assigned keys are used, and the
205
        document with the specified key already exists in the collection, it
206
        will be replaced with the input document.
207

208
        Parameters:
209
        - doc (Any): Document to save
210
        - hint (str): SQL hint for database processing
211

212
        Returns:
213
        SodaDocument: Saved document object
214
        """
215

216
    def truncate(self) -> None:
217
        """
218
        Removes all of the documents in the collection, similarly to what is
219
        done for rows in a table by the TRUNCATE TABLE statement.
220
        """
221
```
222

223
### SODA Document
224

225
Represents a single JSON document with content, metadata, and access methods.
226

227
```python { .api }
228
class SodaDocument:
229
    # Properties
230
    createdOn: str  # Read-only creation timestamp in ISO 8601 format
231
    key: str  # Read-only unique document key
232
    lastModified: str  # Read-only last modified timestamp in ISO 8601 format
233
    mediaType: str  # Read-only media type (usually "application/json")
234
    version: str  # Read-only document version
235

236
    def getContent(self) -> Union[dict, list]:
237
        """
238
        Returns the content of the document as a dictionary or list. This
239
        method assumes that the content is application/json and will raise an
240
        exception if this is not the case.
241

242
        Returns:
243
        Union[dict, list]: Parsed JSON content or None if no content
244
        """
245

246
    def getContentAsBytes(self) -> bytes:
247
        """
248
        Returns the content of the document as a bytes object. If there is no
249
        content, however, None will be returned.
250

251
        Returns:
252
        bytes: Raw document content or None if no content
253
        """
254

255
    def getContentAsString(self) -> str:
256
        """
257
        Returns the content of the document as a string. If the document
258
        encoding is not known, UTF-8 will be used.
259

260
        Returns:
261
        str: Document content as string or None if no content
262
        """
263
```
264

265
### SODA Document Cursor
266

267
Iterator for streaming access to large result sets of documents.
268

269
```python { .api }
270
class SodaDocCursor:
271
    def __iter__(self) -> SodaDocCursor: ...
272
    def __next__(self) -> SodaDocument: ...
273

274
    def close(self) -> None:
275
        """
276
        Close the cursor now, rather than whenever __del__ is called. The
277
        cursor will be unusable from this point forward; an Error exception
278
        will be raised if any operation is attempted with the cursor.
279
        """
280
```
281

282
### SODA Operation
283

284
Fluent interface for building complex queries and operations on documents.
285

286
```python { .api }
287
class SodaOperation:
288
    def count(self) -> int:
289
        """
290
        Returns a count of the number of documents in the collection that match
291
        the criteria. If skip() or limit() were called on this object, an
292
        exception is raised.
293

294
        Returns:
295
        int: Number of matching documents
296
        """
297

298
    def fetchArraySize(self, value: int) -> SodaOperation:
299
        """
300
        This is a tuning method to specify the number of documents that are
301
        internally fetched in batches by calls to getCursor() and
302
        getDocuments().
303

304
        Parameters:
305
        - value (int): Batch size (0 for default of 100)
306

307
        Returns:
308
        SodaOperation: Self for method chaining
309
        """
310

311
    def filter(self, value: Union[dict, str]) -> SodaOperation:
312
        """
313
        Sets a filter specification for complex document queries and ordering
314
        of JSON documents. Filter specifications must be provided as a
315
        dictionary or JSON-encoded string.
316

317
        Parameters:
318
        - value (Union[dict, str]): Filter specification
319

320
        Returns:
321
        SodaOperation: Self for method chaining
322
        """
323

324
    def getCursor(self) -> SodaDocCursor:
325
        """
326
        Returns a SodaDocCursor object that can be used to iterate over the
327
        documents that match the criteria.
328

329
        Returns:
330
        SodaDocCursor: Cursor for iterating documents
331
        """
332

333
    def getDocuments(self) -> list:
334
        """
335
        Returns a list of SodaDocument objects that match the criteria.
336

337
        Returns:
338
        list: List of matching documents
339
        """
340

341
    def getOne(self) -> Union[SodaDocument, None]:
342
        """
343
        Returns a single SodaDocument object that matches the criteria. Note
344
        that if multiple documents match the criteria only the first one is
345
        returned.
346

347
        Returns:
348
        Union[SodaDocument, None]: First matching document or None
349
        """
350

351
    def hint(self, value: str) -> SodaOperation:
352
        """
353
        Specifies a hint that will be provided to the SODA operation when it is
354
        performed. This is expected to be a string in the same format as SQL
355
        hints but without any comment characters.
356

357
        Parameters:
358
        - value (str): SQL hint string
359

360
        Returns:
361
        SodaOperation: Self for method chaining
362
        """
363

364
    def lock(self) -> SodaOperation:
365
        """
366
        Specifies whether the documents fetched from the collection should be
367
        locked (equivalent to SQL "select for update").
368

369
        Returns:
370
        SodaOperation: Self for method chaining
371
        """
372

373
    def key(self, value: str) -> SodaOperation:
374
        """
375
        Specifies that the document with the specified key should be returned.
376
        This causes any previous calls made to this method and keys() to be
377
        ignored.
378

379
        Parameters:
380
        - value (str): Document key to match
381

382
        Returns:
383
        SodaOperation: Self for method chaining
384
        """
385

386
    def keys(self, value: list) -> SodaOperation:
387
        """
388
        Specifies that documents that match the keys found in the supplied
389
        sequence should be returned. This causes any previous calls made to
390
        this method and key() to be ignored.
391

392
        Parameters:
393
        - value (list): List of document keys to match
394

395
        Returns:
396
        SodaOperation: Self for method chaining
397
        """
398

399
    def limit(self, value: int) -> SodaOperation:
400
        """
401
        Specifies that only the specified number of documents should be
402
        returned. This method is only usable for read operations such as
403
        getCursor() and getDocuments().
404

405
        Parameters:
406
        - value (int): Maximum number of documents to return
407

408
        Returns:
409
        SodaOperation: Self for method chaining
410
        """
411

412
    def remove(self) -> int:
413
        """
414
        Removes all of the documents in the collection that match the criteria.
415
        The number of documents that have been removed is returned.
416

417
        Returns:
418
        int: Number of documents removed
419
        """
420

421
    def replaceOne(self, doc: Any) -> bool:
422
        """
423
        Replaces a single document in the collection with the specified
424
        document. The input document can be a dictionary or list or an existing
425
        SODA document object.
426

427
        Parameters:
428
        - doc (Any): Replacement document
429

430
        Returns:
431
        bool: True if a document was replaced, False otherwise
432
        """
433

434
    def replaceOneAndGet(self, doc: Any) -> SodaDocument:
435
        """
436
        Similarly to replaceOne(), this method replaces a single document in
437
        the collection with the specified document. The only difference is that
438
        it returns a SodaDocument object.
439

440
        Parameters:
441
        - doc (Any): Replacement document
442

443
        Returns:
444
        SodaDocument: Replaced document object
445
        """
446

447
    def skip(self, value: int) -> SodaOperation:
448
        """
449
        Specifies the number of documents that match the other criteria that
450
        will be skipped. This method is only usable for read operations such as
451
        getCursor() and getDocuments().
452

453
        Parameters:
454
        - value (int): Number of documents to skip
455

456
        Returns:
457
        SodaOperation: Self for method chaining
458
        """
459

460
    def version(self, value: str) -> SodaOperation:
461
        """
462
        Specifies that documents with the specified version should be returned.
463
        Typically this is used with key() to implement optimistic locking.
464

465
        Parameters:
466
        - value (str): Document version to match
467

468
        Returns:
469
        SodaOperation: Self for method chaining
470
        """
471
```
472

473
## Usage Examples
474

475
```python
476
import oracledb
477

478
# Basic SODA usage
479
with oracledb.connect(user="user", password="pwd", dsn="localhost/orclpdb") as connection:
480
    # Get SODA database
481
    soda_db = connection.getSodaDatabase()
482
    
483
    # Create or open a collection
484
    collection = soda_db.createCollection("employees")
485
    
486
    # Insert a document
487
    doc_content = {"name": "John Doe", "department": "Engineering", "salary": 75000}
488
    collection.insertOne(doc_content)
489
    
490
    # Query documents
491
    documents = collection.find().filter({"department": "Engineering"}).getDocuments()
492
    for doc in documents:
493
        print(f"Employee: {doc.getContent()}")
494
    
495
    # Update a document
496
    result = collection.find().key("some_key").replaceOne({"name": "Jane Doe", "department": "Marketing"})
497
    
498
    # Remove documents
499
    removed_count = collection.find().filter({"salary": {"$lt": 50000}}).remove()
500
    print(f"Removed {removed_count} documents")
501
```