If you like your Postgres doing document goodness, then you'll want to use this interface. Just include it in your module and you can work directly with JSONB in PostgreSQL. We've tried to keep reasonable parity with the Query interface, but there are some concepts here that are a bit different.
This entire module is predicated on a very particular structure for your document table. You don't need to
create this table yourself, we'll do it for you on save/2 if it does not exist.
If you want to create it yourself, the SQL is:
create table if not exists NAME(
id bigint generated by default as identity primary key,
body jsonb not null,
search tsvector,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
-- index the search and jsonb fields
create index if not exists idx_NAME_search on NAME using GIN(search);
create index if not exists idx_NAME on NAME using GIN(body jsonb_path_ops);Using this structure we can apply some convention, making this interface particularly compelling. Of primary note is the Full Text search, this ability (and speed) is what sets PostgreSQL above other document systems.
Summary
Functions
This is analogous to filter with the Query module, however this method is highly optimized for JSONB as it uses the @> (contains)
operator. This flexes the GIN index created for your table (see above).
Specifies the table or view you want to query. 'with' is an alias for the db/1 function using
a string as a table name. This is useful for specifying a table within a schema.
Queries a table using the existence operator. Not a good query to run on a large table as it needs to do a full scan in order to match,
and can't use the built-in index, however if you need to query embedded arrays this is a good choice. If possible, use contains/2
Queries a table using matching string criteria. Not a good query to run on a large table as it needs to do a full scan in order to match,
and can't use the built-in index, however if you need to do a specialized query this is a good choice. If possible, use contains/2
Queries a table using matching criteria. Not a good query to run on a large table as it needs to do a full scan in order to match,
and can't use the built-in index, however if you need to do a specialized query this is a good choice. If possible, use contains/2
Finds a document based on ID using the Primary Key index. An optimized query for finding a single document.
Alias for Query limit
Alias for Query offset
Performs a Full Text query on the indexed search column, which searchable/2 fills on save.
The term goes through websearch_to_tsquery, so anything typed into a search box works.
Marks a set of fields for indexing during save.
Creates a SELECT command based on the assembled pipeline.
Sorts the query based on the supplied criteria.
Functions
This is analogous to filter with the Query module, however this method is highly optimized for JSONB as it uses the @> (contains)
operator. This flexes the GIN index created for your table (see above).
Document predicates (contains/2, filter/3, filter/4, exists/3) each set the whole
where clause; the last one in a pipeline wins. To combine conditions, put them in one
contains/2 map or write the SQL in filter/3.
criteria: - A list of elements to look for. This list must be complete, partial matches won't work.
Example:
db(:user_docs)
|> contains(email: "test@test.com")
|> firstSpecifies the table or view you want to query. 'with' is an alias for the db/1 function using
a string as a table name. This is useful for specifying a table within a schema.
:table - the name of the table you want to query, such as :users or membership.user_docs
Example
result = db(:users)
|> to_list
result = with("membership.user_docs")
|> to_list- Deletes a document based on the filter (if any)
- Deletes a document with the given id
Queries a table using the existence operator. Not a good query to run on a large table as it needs to do a full scan in order to match,
and can't use the built-in index, however if you need to query embedded arrays this is a good choice. If possible, use contains/2
Example:
return = db(:user_docs)
|> exists(:pets, "skippy") # :pets is an array
|> to_listQueries a table using matching string criteria. Not a good query to run on a large table as it needs to do a full scan in order to match,
and can't use the built-in index, however if you need to do a specialized query this is a good choice. If possible, use contains/2
Example:
return = db(:user_docs)
|> filter("body -> 'email' = $1", res.email)
|> firstQueries a table using matching criteria. Not a good query to run on a large table as it needs to do a full scan in order to match,
and can't use the built-in index, however if you need to do a specialized query this is a good choice. If possible, use contains/2
Example:
return = db(:user_docs)
|> filter(:id, ">", 100)
|> to_listFinds a document based on ID using the Primary Key index. An optimized query for finding a single document.
Example:
return = db(:user_docs)
|> find(12)Alias for Query limit
Alias for Query offset
Performs a Full Text query on the indexed search column, which searchable/2 fills on save.
The term goes through websearch_to_tsquery, so anything typed into a search box works.
Example:
users = db(:user_docs)
|> search("test.com")Performs a Full Text query using a full table scan. Not a good choice for larger tables. If possible,
specify your search columns on save/3 and use search/2.
Example:
users = db(:user_docs)
|> search(for: "test.com", in: [:email])Marks a set of fields for indexing during save.
Example:
product = %{sku: "TEST_1", name: "Test Product", description: "Just a test"}
return = db(:products)
|> searchable([:name, :description])
|> save(product)Creates a SELECT command based on the assembled pipeline.
Sorts the query based on the supplied criteria.
Example:
return = db(:user_docs)
|> exists(:pets, "skippy") # :pets is an array
|> sort(:city)
|> to_list