BitBadger.Documents
Advanced Usage: Related Documents / Custom Queries

NOTE: This page is longer than the ideal documentation page. Understanding how to assemble custom queries requires understanding how data is stored, and the list of ways to retrieve information can be… a lot. The hope is that one reading will serve as education, and the lists of options will serve as reference lists that will assist you in crafting your queries.

Overview

Document stores generally have fewer relationships than traditional relational databases, particularly those that arise when data is structured in Third Normal Form; related collections are stored in the document, and ever-increasing surrogate keys (a la sequences and such) do not play well with distributed data. Unless all data is stored in a single document, though, there will still be a natural relation between documents.

Thinking back to our earlier examples, we did not store the collection of rooms in each hotel's document; each room is its own document and contains the ID of the hotel as one of its properties.

// C#
public class Hotel
{
    public string Id { get; set; } = "";
    // ... more properties
}

public class Room
{
    public string Id { get; set; } = "";
    public string HotelId { get; set; } = "";
    // ... more properties
}

// F#
[<CLIMutable>]
type Hotel =
    {   Id: string
        // ... more fields
    }

[<CLIMutable>]
type Room =
    {   Id: string
        HotelId: string
        // ... more fields
    }

The CLIMutable attribute is required on record types that are instantiated by the CLR; this attribute generates a zero-parameter constructor.

Document Table SQL in Depth

The library creates tables with a data column of type JSONB (PostgreSQL) or TEXT (SQLite), with a unique index on the configured ID name that serves as the primary key (for these examples, we'll assume it's the default Id). The indexes created by the library all apply to the data column. The by-ID query for a hotel would be…

SELECT data FROM hotel WHERE data ->> 'Id' = @id

...with the ID passed as the @id parameter.

Using a “building block” method/function Query.WhereById will create the data ->> 'Id' = @id criteria using the configured ID name.

Finding all the rooms for a hotel, using our indexes we created earlier, could use a field comparison query…

SELECT data FROM room WHERE data ->> 'HotelId' = @field

...with @field being “abc123”; PostgreSQL could also use a JSON containment query…

SELECT data FROM room WHERE data @> @criteria

...with something like new { HotelId = "abc123" } passed as the matching document in the @criteria parameter.

So far, so good; but, if we're looking up a room, we do not want to have to make 2 queries just to also be able to display the hotel's name. The WHERE clause on the first query above uses the expression data ->> 'Id'; this extracts a field from a JSON column as TEXT in PostgreSQL (or “best guess” in SQLite, but usually text). Since this is the value our unique index indexes, and we are using a relational database, we can write an efficient JOIN between these two tables.

SELECT r.data, h.data AS hotel_data
  FROM room r
       INNER JOIN hotel h ON h.data ->> 'Id' = r.data ->> 'HotelId'
 WHERE r.data ->> 'Id' = @id

(This syntax would work without the unique index; for PostgreSQL, it would default to using the GIN index (Full or Optimized), if it exists, but it wouldn't be quite as efficient as a zero-or-one unique index lookup. For SQLite, this would result in a full table scan. Both PostgreSQL and SQLite also support a -> operator, which extracts the field as a JSON value instead of its text.)

Using Building Blocks

Most of the data access methods in both libraries are built up from query fragments and reusable functions; these are exposed for use in building custom queries.

Queries

For every method or function described in Basic Usage, the Query static class/module contains the query for that operation. Both the parent and implementation namespaces have a Query module; in C#, you'll need to qualify the implementation module namespace.

In BitBadger.Documents.Query, you'll find:

• SelectFromTable takes a table name and generates a SELECT statement from that name.
• WhereByField takes a set of field criteria and a parameter name. Field has constructor functions for each Op it supports; Op is short for “operation” and uses mostly 2-letter operation abbreviations to specify the operation. These functions generally take a field name and a value, though the latter two do not require a value.
  • EQ uses = to create an equality comparison
  • GT uses > to create a greater-than comparison
  • GE uses >= to create a greater-than-or-equal-to comparison
  • LT uses < to create a less-than comparison
  • LE uses <= to create a less-than-or-equal-to comparison
  • NE uses <> to create a not-equal comparison
  • EX uses IS NOT NULL to create an existence comparison
  • NEX uses IS NULL to create a non-existence comparison; fields are considered null if they are either not part of the document, or if they are part of the document but explicitly set to null
• WhereById takes a parameter name and generates a field EQ comparison against the configured ID field.
• Definition contains methods/functions to ensure tables, their keys, and field indexes exist.
• Insert, Save, and Update are the queries for those actions; all specify an @data parameter, and Update also specifies an @id parameter against the configured ID field
• Count, Exists, Find, and Delete all require at least a table name. Their ById queries specify an @id parameter against the configured ID field (there is no Count.ById). Their ByField queries require a Field instance and specify a @field parameter against the specified field (unless Op.EX or Op.NEX are used). Count has an All query which takes no further parameters and specifies no parameters.

Within each implementation's Query module, there are Patch and RemoveFields queries whose parameters follow the same pattern. (The syntax for this differs between PostgreSQL and SQLite.) For PostgreSQL, there are also ByContains and ByJsonPath queries, which specify @criteria and @path parameters (respectively). @criteria can be any object, @path should be a string.

That's a lot of reading! Some examples a bit below will help this make sense.

Parameters

Traditional ADO.NET data access involves creating a connection object, then adding parameters to that object. This library follows a more declarative style, where parameters are passed via lists or IEnumerable collections. To assist with creating these collections, each implementation has some helper functions to assist with common parameters. For C#, these calls will need to be prefixed with Parameters; for F#, this module is auto-opened. This is one area where names differ in other than just casing, so both will be listed.

• Parameters.Id / idParam generate an @id parameter with the ToString()ed value of the ID passed.
• Parameters.Json / jsonParam generate a user-provided-named JSON-formatted parameter for the value passed (this can be used for PostgreSQL's JSON containment queries as well)
• Parameters.AddField / addFieldParam append a field parameter with the name and criteria passed, appending them to the current parameter list
• Parameters.FieldNames / fieldNameParams create parameters for the list of field names to be removed; for PostgreSQL, this returns a single parameter, while SQLite returns a list of parameters
• Parameters.None / noParams is an empty set of parameters, and can be cleaner and convey intent better than something like new[] { }

If you need a parameter beyond these, both NpgsqlParameter and SqliteParameter have a name-and-value constructor; that isn't many more keystrokes.

Results

The Results module is implementation specific. Both libraries provide Results.FromData<T>, which deserializes a data column into the requested type; and FromDocument<T>, which does the same thing, but allows the column to be named as well. We'll see how we can use these in further examples. As with parameters, C# users need to qualify the class name, but the module is auto-opened for F#.

Putting It All Together

The Custom static class/module has four methods/functions:

• List requires a query, parameters, and a mapping function, and returns a list of documents.
• Single requires a query, parameters, and a mapping function, and returns one or no documents (C# TDoc?, F# 'TDoc option)
• Scalar requires a query, parameters, and a mapping function, and returns a scalar value (non-nullable; used for counts, existence, etc.)
• NonQuery requires a query and parameters and has no return value

Within each library, every other call is written in terms of Custom.List, Custom.Scalar, or Custom.NonQuery; your custom queries will use the same path the provided ones do!

Let's jump in with an example. When we query for a room, let's say that we also want to retrieve its hotel information as well. We saw the query above, but here is how we can implement it using a custom query.

// C#, All
    // return type is Tuple<Room, Hotel>?
    var data = await Custom.Single(
        $"SELECT r.data, h.data AS hotel_data
            FROM room r
                 INNER JOIN hotel h ON h.data ->> '{Configuration.IdField()}' = r.data ->> 'HotelId'
           WHERE r.{Query.WhereById("@id")}",
        new[] { Parameters.Id("my-room-key") },
        // rdr's type will be RowReader for PostgreSQL, SqliteDataReader for SQLite
        rdr => Tuple.Create(Results.FromData<Room>(rdr), Results.FromDocument<Hotel>("hotel_data", rdr));
    if (data is not null)
    {
        var (room, hotel) = data;
        // do stuff with the room and hotel data
    }

// F#, All
    // return type is (Room * Hotel) option
    let! data =
        Custom.single
            $"""SELECT r.data, h.data AS hotel_data
                  FROM room r
                       INNER JOIN hotel h ON h.data ->> '{Configuration.idField ()}' = r.data ->> 'HotelId'
                 WHERE r.{Query.whereById "@id"}"""
            [ idParam "my-room-key" ]
            // rdr's type will be RowReader for PostgreSQL, SqliteDataReader for SQLite
            fun rdr -> (fromData<Room> rdr), (fromDocument<Hotel> "hotel_data" rdr)
    match data with
    | Some (Room room, Hotel hotel) ->
        // do stuff with room and hotel
    | None -> ()

These queries use Configuration.IdField and WhereById to use the configured ID field. Creating custom queries using these building blocks allows us to utilize the configured value without hard-coding it throughout our custom queries. If the configuration changes, these queries will pick up the new field name seamlessly.

While this example retrieves the entire document, this is not required. If we only care about the name of the associated hotel, we could amend the query to retrieve only that information.

// C#, All
    // return type is Tuple<Room, string>?
    var data = await Custom.Single(
        $"SELECT r.data, h.data ->> 'Name' AS hotel_name
            FROM room r
                 INNER JOIN hotel h ON h.data ->> '{Configuration.IdField()}' = r.data ->> 'HotelId'
           WHERE r.{Query.WhereById("@id")}",
        new[] { Parameters.Id("my-room-key") },
        // PostgreSQL
        row => Tuple.Create(Results.FromData<Room>(row), row.string("hotel_name")));
        // SQLite; could use rdr.GetString(rdr.GetOrdinal("hotel_name")) below as well
        // rdr => Tuple.Create(Results.FromData<Room>(rdr), rdr.GetString(1)));

    if (data is not null)
    {
        var (room, hotelName) = data;
        // do stuff with the room and hotel name
    }

// F#, All
    // return type is (Room * string) option
    let! data =
        Custom.single
            $"""SELECT r.data, h.data ->> 'Name' AS hotel_name
                  FROM room r
                       INNER JOIN hotel h ON h.data ->> '{Configuration.idField ()}' = r.data ->> 'HotelId'
                 WHERE r.{Query.whereById "@id"}"""
            [ idParam "my-room-key" ]
            // PostgreSQL
            fun row -> (fromData<Room> row), row.string "hotel_name"
            // SQLite; could use rdr.GetString(rdr.GetOrdinal("hotel_name")) below as well
            // fun rdr -> (fromData<Room> rdr), rdr.GetString(1)
    match data with
    | Some (Room room, string hotelName) ->
        // do stuff with room and hotel name
    | None -> ()

These queries are amazingly efficient, using 2 unique index lookups to return this data. Even though we do not have a foreign key between these two tables, simply being in a relational database allows us to retrieve this related data.

Revisiting our “take these rooms out of service” SQLite query from the Basic Usage page, here's how that could look using these building blocks (PostgreSQL will accept this query syntax as well, though the parameter types would be different):

// C#, SQLite
    var roomMin = Field.GE("RoomNumber", 221);
    var roomMax = Field.LE("RoomNumber", 240);
    await Custom.NonQuery(
        $"{Sqlite.Query.Patch.ByField("room", roomMin, new { InService = false })}
            AND {Query.WhereByField(roomMax, "@field2")}",
        Parameters.AddField("@field", roomMin, Parameters.AddField("@field2", roomMax, Parameters.None)));

// F#, SQLite
    let roomMin = Field.GE "RoomNumber" 221
    let roomMax = Field.LE "RoomNumber" 240
    do! Custom.nonQuery
            $"""{Query.Patch.byField "room" roomMin {| InService = false |}}
                  AND {Query.whereByField roomMax "@field2"}"""
            (addFieldParam "@field" roomMin (addFieldParam "@field2" roomMax []))

This uses two field comparisons to incorporate the room number range instead of a BETWEEN clause; we would definitely want to have that field indexed if this was going to be a regular query or our data was going to grow beyond a trivial size.

Going Even Further

Updating Data in Place

One drawback to document databases is the inability to update values in place; however, with a bit of creativity, we can do a lot more than we initially think. For a single field, SQLite has a json_set function that takes an existing JSON field, a field name, and a value to which it should be set. This allows us to do single-field updates in the database. If we wanted to raise our rates 10% for every room, we could use this query:

-- SQLite
UPDATE room SET data = json_set(data, 'Rate', data ->> 'Rate' * 1.1)

If we get any more complex, though, Common Table Expressions (CTEs) can help us. Perhaps we decided that we only wanted to raise the rates for hotels in New York, Chicago, and Los Angeles, and we wanted to exclude any brand with the word “Value” in its name. A CTE lets us select the source data we need to craft the update, then use that in the UPDATE's clauses.

-- SQLite
WITH to_update AS
    (SELECT r.data ->> 'Id' AS room_id, r.data ->> 'Rate' AS current_rate, r.data AS room_data
       FROM room r
            INNER JOIN hotel h ON h.data ->> 'Id' = r.data ->> 'HotelId'
      WHERE h.data ->> 'City' IN ('New York', 'Chicago', 'Los Angeles')
        AND LOWER(h.data ->> 'Name') NOT LIKE '%value%')
UPDATE room
   SET data = json_set(to_update.room_data, 'Rate', to_update.current_rate * 1.1)
 WHERE room ->> 'Id' = to_update.room_id

Both PostgreSQL and SQLite provide JSON patching, where multiple fields (or entire structures) can be changed at once. Let's revisit our rate increase; if we are making the rate more than $500, we'll apply a status of “Premium” to the room. If it is less than that, it should keep its same value.

First up, PostgreSQL:

-- PostgreSQL
WITH to_update AS
    (SELECT r.data ->> 'Id' AS room_id, (r.data ->> 'Rate')::decimal AS rate, r.data ->> 'Status' AS status
       FROM room r
            INNER JOIN hotel h ON h.data ->> 'Id' = r.data ->> 'HotelId'
      WHERE h.data ->> 'City' IN ('New York', 'Chicago', 'Los Angeles')
        AND LOWER(h.data ->> 'Name') NOT LIKE '%value%')
UPDATE room
   SET data = data ||
                ('{"Rate":' || to_update.rate * 1.1 || ',"Status":"'
                  || CASE WHEN to_update.rate * 1.1 > 500 THEN 'Premium' ELSE to_update.status END
                  || '"}')
 WHERE room ->> 'Id' = to_update.room_id

In SQLite:

-- SQLite
WITH to_update AS
    (SELECT r.data ->> 'Id' AS room_id, r.data ->> 'Rate' AS rate, r.data ->> 'Status' AS status
       FROM room r
            INNER JOIN hotel h ON h.data ->> 'Id' = r.data ->> 'HotelId'
      WHERE h.data ->> 'City' IN ('New York', 'Chicago', 'Los Angeles')
        AND LOWER(h.data ->> 'Name') NOT LIKE '%value%')
UPDATE room
   SET data = json_patch(data, json(
                '{"Rate":' || to_update.rate * 1.1 || ',"Status":"'
                  || CASE WHEN to_update.rate * 1.1 > 500 THEN 'Premium' ELSE to_update.status END
                  || '"}'))
 WHERE room ->> 'Id' = to_update.room_id

For PostgreSQL, ->> always returns text, so we need to cast the rate to a number. In either case, we do not want to use this technique for user-provided data; however, in place, it allowed us to complete all of our scenarios without having to load the documents into our application and manipulate them there.

Updates in place may not need parameters (though it would be easy to foresee a “rate adjustment” feature where the 1.1 adjustment was not hard-coded); in fact, none of the samples in this section used the document libraries at all. These queries can be executed by Custom.NonQuery, though, providing parameters as required.

Using This Library for Non-Document Queries

The Custom methods/functions can be used with non-document tables as well. This may be a convenient and consistent way to access your data, while delegating connection management to the library and its configured data source.

Let's walk through a short example using C# and PostgreSQL:

// C#, PostgreSQL
    using Npgsql.FSharp; // Needed for RowReader and Sql types
    using static CommonExtensionsAndTypesForNpgsqlFSharp; // Needed for Sql functions

    // Stores metadata for a given user
    public class MetaData
    {
        public string Id { get; set; } = "";
        public string UserId { get; set; } = "";
        public string Key { get; set; } = "";
        public string Value { get; set; } = "";
    }

    // Static class to hold mapping functions
    public static class Map
    {
        // These parameters are the column names from the underlying table
        public MetaData ToMetaData(RowReader row) =>
            new MetaData
            {
                Id = row.string("id"),
                UserId = row.string("user_id"),
                Key = row.string("key"),
                Value = row.string("value")
            };
    }

    // somewhere in a class, retrieving data
    public Task<List<MetaData>> MetaDataForUser(string userId) =>
        Document.Custom.List("SELECT * FROM user_metadata WHERE user_id = @userId",
            new { Tuple.Create("@userId", Sql.string(userId)) },
            Map.ToMetaData);

For F#, the using static above is not needed; that module is auto-opened when Npgsql.FSharp is opened. For SQLite in either language, the mapping function uses a SqliteDataReader object, which implements the standard ADO.NET DataReader functions of Get[Type](idx) (and GetOrdinal(name) for the column index).

« Back to Advanced Usage

« BitBadger.Documents Home View Source Repo