Find names of fields in the database collection

Given part of the name of a field of interest to the user, this function returns the full field names used in records that were previously loaded into a collection (using ctrLoadQueryIntoDb). Only names of fields that have a value in the collection can be returned. Set sample = FALSE to force screening all records in the collection for field names, see below.

Usage

dbFindFields(namepart = ".*", con, sample = TRUE, verbose = FALSE)

Arguments

namepart: A character string (can be a regular expression, including Perl-style) to be searched among all field names (keys) in the collection, case-insensitive. The default `".*"` lists all fields.
con: A connection object, see section `Databases` in ctrdata.
sample: If TRUE (default), uses a sample of only 5 trial records per register to identify fields, to rapidly return a possibly incomplete set of field names. If FALSE, uses all trial records in the collection, which will take more time with more trials but ensures to returns all names of all fields in the collection.
verbose: If TRUE, prints additional information (default FALSE).

Value

Vector of strings with full names of field(s) found, ordered by register and alphabet, see examples. Names of the vector are the names of the register holding the respective fields. The field names can be fed into dbGetFieldsIntoDf to extract the data for the field(s) from the collection into a data frame.

Details

The full names of child fields are returned in dot notation (e.g., clinical_results.outcome_list.outcome.measure.class_list.class.title) In addition, names of parent fields (e.g., clinical_results) are returned. Data in parent fields is typically complex (nested), see dfTrials2Long for easily handling it. For field definitions of the registers, see "Definition" in ctrdata-registers. Note: When dbFindFields is first called after ctrLoadQueryIntoDb, it will take a moment.

Examples


dbc <- nodbi::src_sqlite(
    dbname = system.file("extdata", "demo.sqlite", package = "ctrdata"),
    collection = "my_trials"
)
#> RSQLite version has enabled accelerating docdb_create() and docdb_update() functions when used with value = <NDJSON file name>.

dbFindFields(namepart = "date", con = dbc)[1:5]
#> Finding fields in database collection
#>  (sampling 5 trial records per register) 
#>  . 
#>  . 
#>  . 
#>  . 
#>  . 
#>  . 
#>  . 
#>  . 
#> 
#> Field names cached for this session.
#>                                                EUCTR 
#>             "n_date_of_competent_authority_decision" 
#>                                                EUCTR 
#>                 "n_date_of_ethics_committee_opinion" 
#>                                                EUCTR 
#>              "p_date_of_the_global_end_of_the_trial" 
#>                                                EUCTR 
#> "trialChanges.globalAmendments.globalAmendment.date" 
#>                                                EUCTR 
#>                 "trialInformation.analysisStageDate" 

# view all 3350+ fields from all registers:

allFields <- dbFindFields(con = dbc, sample = FALSE)
#> Finding fields in database collection
#>  (may take some time) 
#>  . 
#>  . 
#>  . 
#>  . 
#>  . 
#> 
#> Field names cached for this session.

if (interactive()) View(data.frame(
  register = names(allFields),
  field = allFields))