Get documents with a filtering query

Get documents with a 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() or src_postgres()

key

(character) A key as name of the container (corresponds to parameter collection for MongoDB, dbname for CouchDB, index for Elasticsearch and to a table name for SQLite and for PostgreSQL)

query

(character) A JSON query string, see examples. Can use multiple comparisons / tests (e.g., '$gt', '$ne', '$in', '$regex'), with at most one logic operator ('$and' if not specified, or '$or'), see examples.

...

Optionally, fields a JSON string of fields to be returned from anywhere in the tree (dot paths notation), see examples.

Value

Data frame with requested data, may have nested lists in columns

Note

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

Main functions used per database:

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

• SQLite: SQL query using 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

if (FALSE) {
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 = '{"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}}]}')
}