docdb_query: Get documents or parts with filtering query

In nodbi: 'NoSQL' Database Connector

Get documents or parts with filtering query

Description

Get documents or parts with filtering query

Usage

docdb_query(src, key, query, ...)

Arguments

src	Source object, result of call to any of functions src_mongo(), src_sqlite(), src_elastic(), src_couchdb() src_duckdb() or src_postgres()
key	(character) The name of the container in the database backend (corresponds to collection for MongoDB, dbname for CouchDB, index for Elasticsearch and to a table name for DuckDb, SQLite and PostgreSQL)
query	(character) A JSON query string, see examples. Can use comparisons / tests (e.g., '$gt', '$ne', '$in', '$regex'), with at most one logic operator ('$and' if not specified, or '$or'), see examples.
...	Optionally, specify fields as a JSON string of fields to be returned from anywhere in the tree, see examples.

Value

Data frame with requested documents, may have nested lists in columns. If query = "{}" and fields are not specified, consider using docdb_get().

Note

A dot in query or fields is interpreted as a dot path; it is not supported to have a dot within the key / name of a field.

Main functions used per database:

• MongoDB: find() in mongolite::mongo()

• SQLite: SQL query using built-in json_tree()

• Elasticsearch: elastic::Search()

• CouchDB: sofa::db_query()

• PostgreSQL: SQL query using built-in jsonb_build_object()

• DuckDB: SQL using built-in json_extract()

Examples

## Not run: 
src <- src_sqlite()
docdb_create(src, "mtcars", mtcars)
docdb_query(src, "mtcars", query = '{"mpg":21}')
docdb_query(src, "mtcars", query = '{"mpg":21, "gear": {"$lte": 4}}')
docdb_query(src, "mtcars", query = '{"mpg":21}', fields = '{"_id":0, "mpg":1, "cyl":1}')
docdb_query(src, "mtcars", query = '{"_id": {"$regex": "^.+0.*$"}}', fields = '{"gear": 1}')
# complex query, not supported for src_elastic and src_couchdb backends at this time:
docdb_query(src, "mtcars", query = '{"$and": [{"mpg": {"$lte": 18}}, {"gear": {"$gt": 3}}]}')

## End(Not run)

nodbi documentation built on Sept. 24, 2023, 1:08 a.m.