🚀 Fractal Platform Query Guide

A beginner-friendly guide to database queries

0. Sample Data - Users Collection

Throughout this guide, we'll use a "Users" collection with 3 documents. Understanding this data will help you follow along with all the examples.

Document 1: Bob (ID: 0000000001)

{
    "Birthday": "1990-01-01",
    "IsManager": true,
    "Name": "Bob",
    "Weight": 70,
    "Car": {
        "Model": "Subaru"
    },
    "Childs": [
        {
            "Age": 3,
            "Name": "Ted",
            "Numbers": [4, 5, 6]
        },
        {
            "Age": 5,
            "Name": "Mike",
            "Numbers": [7, 8, 9]
        },
        {
            "Age": 20,
            "Name": "Boby"
        }
    ],
    "Facet1": {
        "Attr1": false,
        "Attr2": true,
        "Attr3": false
    },
    "Facet2": {
        "Attr1": false,
        "Attr2": true,
        "Attr3": false
    },
    "Jobs": [
        {
            "Company": "Microsoft",
            "Position": "Engineer"
        },
        {
            "Company": "Amazon",
            "Position": "Manager"
        }
    ],
    "Numbers": [1]
}

Document 2: Ted (ID: 0000000002)

{
    "Name": "Ted",
    "Weight": 75,
    "IsManager": true,
    "Jobs": [
        {
            "Company": "Microsoft",
            "Position": "Manager"
        }
    ]
}

Document 3: Tim (ID: 0000000003)

{
    "Age": 25,
    "Name": "Tim",
    "Car": {
        "Engine": "3.0",
        "Model": "VW"
    },
    "Childs": [
        {
            "Age": "5",
            "Name": "Ted"
        }
    ],
    "Numbers": [7, 2, 3, 7],
    "Participants": [
        {
            "Choose": null,
            "Gender": "Boy",
            "Name": "Hello",
            "Questions": [
                "Question1",
                "Question2",
                "Question3"
            ]
        }
    ]
}
💡 Key Points:
  • Bob has 3 children, works at Microsoft and Amazon, and drives a Subaru
  • Ted is a manager at Microsoft with weight 75
  • Tim is 25 years old, drives a VW, and has 1 child
  • Not all documents have the same fields - this is normal in NoSQL databases!

1. Query Basics

What is a Query?

A query is a way to ask your database for information. Think of it like asking questions to your data storage.

Setting Up Your Collection

Before you can query data, you need to specify which collection (table) you want to work with. In the new format, you pass the collection name directly to the query methods:

// Old format (deprecated)
Client.SetDefaultCollection("Users")
    .GetDoc(1)

// New format
FirstDocOf("Users")
💡 Tip: A collection is like a folder that holds similar types of data. For example, all user information goes in the "Users" collection.

Getting a Single Document

To get one specific document by its ID:

FirstDocOf("Users")
    .Count();

This finds the first document and counts how many were found (should be 1 if it exists).

To get a document by specific ID:

uint docID = 1;
DocsWhere("Users", docID)
    .Count();

Getting All Documents

To get everything from a collection:

DocsOf("Users")
    .Count();

This returns all documents in the Users collection.

💡 Result: This will return 3 documents (Bob, Ted, and Tim) in the initial state, 2 after some tests run.

2. Filtering Data with GetWhere

Simple Filtering

To find documents that match certain conditions, use DocsWhere:

DocsWhere("Users", new { Name = "Bob" })
    .Select<UserInfo>();

This finds all users with the name "Bob".

💡 Result: This returns 1 document - Bob's record from our Users collection.

Multiple Conditions

You can filter by multiple fields at once:

DocsWhere("Users", new {
    Name = "Bob",
    Car = new {
        Model = "Subaru"
    }
})
    .Select<UserInfo>();

This finds users named "Bob" who have a Subaru car. All conditions must match (AND logic).

💡 Result: Returns Bob's document because he has a Car with Model "Subaru".

Using JSON Format

You can also write filters as JSON strings:

DocsWhere("Users", "{'Car':{'Engine':2}}")
    .Select<UserInfo>();
💡 Tip: JSON format is more flexible and allows you to use special operators we'll learn about later.
⚠️ Note: Bob's Car doesn't have an "Engine" field in the original data, but this example shows the syntax for nested queries.

Combining AND and OR

Use AndWhere and OrWhere to combine conditions:

// Find users named "Bob" AND age 20
DocsWhere("Users", "{'Name':'Bob'}")
    .AndWhere("{'Age':20}")
    .Count();

// Find users named "Bob" OR age 20
DocsWhere("Users", "{'Name':'Bob'}")
    .OrWhere("{'Age':20}")
    .Count();

3. Conditional Queries

Comparison Operators

You can use special operators to compare values:

Operator Meaning Example
Less() Less than Age < 21
LessOrEqual() Less than or equal Age ≤ 20
Great() Greater than Age > 10
GreatOrEqual() Greater than or equal Age ≥ 20
Not() Not equal to Age ≠ 20

Examples

// Find users younger than 21
DocsWhere("Users", "{'Age':Less(21)}")
    .Select<UserInfo>();
    // Result: Returns Bob (Age: 20)

// Find users 20 or older
DocsWhere("Users", "{'Age':GreatOrEqual(20)}")
    .Select<UserInfo>();
    // Result: Returns Bob (Age: 20)

// Find users not age 20
DocsWhere("Users", "{'Age':Not(20)}")
    .Select<UserInfo>();
    // Result: Returns 0 documents

Range Queries

To find values between two numbers or dates:

// Age between 20 and 21
DocsWhere("Users", "{'Age':Range(20,21)}")
    .Select<UserInfo>();
    // Result: Returns Bob (Age: 20)

// Birthday between two dates
DocsWhere("Users", "{'Birthday':Range('1980-01-01','2000-01-01')}")
    .Select<UserInfo>();
    // Result: Returns Bob (Birthday: 1990-01-01)

Pattern Matching

Use Template() to match patterns (like wildcards):

// Find ages starting with '2' (like 20, 21, 29)
DocsWhere("Users", "{'Age':Template('2*')}")
    .Select<UserInfo>();
    // Result: Returns Bob (Age: 20)

Any of Multiple Values

Use Any() to check if a value matches any of several options:

// Find users with age 20 OR 21
DocsWhere("Users", "{'Age':Any(20,21)}")
    .Select<UserInfo>();
    // Result: Returns Bob (Age: 20)

4. Working with Arrays

Finding Items in Arrays

To find documents where an array contains a specific value:

// Find users who have number 2 in their Numbers array
DocsWhere("Users", "{'Numbers':[2]}")
    .Select<UserInfo>();
    // Result: Returns Bob (Numbers: [1,2,3] after updates)

Multiple Values (IN Logic)

Check if ALL specified values exist in the array:

// Find users who have BOTH 1 AND 2 in Numbers
DocsWhere("Users", "{'Numbers':[1,2]}")
    .Select<UserInfo>();
    // Result: Returns Bob (after updates, has [1,2,3])

ANY Logic

Check if ANY of the specified values exist:

// Find users who have 1 OR 5 in Numbers
DocsWhere("Users", "{'Numbers':[Any,1,5]}")
    .Select<UserInfo>();
    // Result: Returns Bob (has 1 in Numbers)

NOT IN Logic

Check if a value does NOT exist in the array:

// Find users who DON'T have 2 in Numbers
DocsWhere("Users", "{'Numbers':[Nin,2]}")
    .Select<UserInfo>();
    // Result: Initially returns 0, later returns Bob after 2 is deleted

Finding Objects in Arrays

You can search for objects with specific properties:

// Find users who have a child named "Ted"
DocsWhere("Users", "{'Childs':[{'Name':'Ted'}]}")
    .Select<UserInfo>();
    // Result: Returns Bob (has a child named Ted)

// Find users with a Manager job position
DocsWhere("Users", "{'Jobs':[{'Position':'Manager'}]}")
    .Select<UserInfo>();
    // Result: Returns Bob (Amazon - Manager) and Ted (Microsoft - Manager)
💡 Tip: When filtering array objects, the system looks for any item in the array that matches all the specified properties.

Counting Array Items

To count how many items are in an array:

// Count how many jobs a user has
FirstDocOf("Users")
    .Count("{'Jobs':[{'Company':$}]}");
// Result: Returns 3 (Bob has initially 2 jobs, then gets a 3rd)

5. Adding Data (Insert)

Adding a New Document

To create a completely new document:

AddDoc("Users", new UserInfo {
    Name = "Tim"
});

Adding Fields to Existing Documents

Use Update to add new fields:

// Add age to Tim's document
ModifyDocsWhere("Users", "{'Name':'Tim'}")
    .Update("{'Age':15}");

Adding Arrays

To add an array of values:

ModifyDocsWhere("Users", "{'Name':'Tim'}")
    .Update("{'Numbers':[1,2,3]}");

Adding Objects

To add nested objects:

ModifyDocsWhere("Users", "{'Name':'Tim'}")
    .Update("{'Car':{'Engine':'2.0', 'Model':'VW'}}");

Adding Objects to Arrays

To add an object as an item in an array:

ModifyDocsWhere("Users", "{'Name':'Tim'}")
    .Update("{'Childs':[Add,{'Name':'Ted','Age':'5'}]}");
⚠️ Warning: Using Update with an array will replace the entire array. To add items to an existing array, see the "Updating Arrays" section.

6. Updating Data

Updating Simple Fields

To change the value of a field:

ModifyDocsWhere("Users", "{'Name':'Tim'}")
    .Update("{'Age':25}");

Updating Nested Fields

To update fields inside objects:

ModifyDocsWhere("Users", "{'Name':'Tim'}")
    .Update("{'Car':{'Engine':'3.0'}}");

Math Operations

You can add or subtract numbers:

// Add 1 to age
ModifyDocsWhere("Users", "{'Name':'Tim'}")
    .Update("{'Age':Add(1)}");

// Subtract 1 from age
ModifyDocsWhere("Users", "{'Name':'Tim'}")
    .Update("{'Age':Sub(1)}");

Adding Items to Arrays

Use the Add operator to append items:

// Add number 4 to the Numbers array
ModifyDocsWhere("Users", "{'Name':'Tim'}")
    .Update("{'Numbers':[Add,4]}");

// Add a new child object
ModifyDocsWhere("Users", "{'Name':'Bob'}")
    .Update("{'Childs':[Add,{'Name':'Boby','Age':20}]}");

Updating Array Items by Index

Use @ with an index number to update specific positions:

// Update the first item (index 0)
ModifyDocsWhere("Users", "{'Name':'Tim'}")
    .Update("{'Numbers':[@0,7]}");

// Update the last item
ModifyDocsWhere("Users", "{'Name':'Tim'}")
    .Update("{'Numbers':[Last,7]}");
💡 Tip: Arrays are zero-indexed, meaning the first item is at position 0, the second at position 1, and so on.

7. Selecting Specific Fields

Getting All Fields

By default, Select returns everything:

DocsWhere("Users", "{'Name':'Bob'}")
    .Select<UserInfo>();
    // Result: Returns Bob's complete document with all fields:
    // Name: "Bob", Birthday: "1990-01-01", Weight: 70,
    // IsManager: true, Car, Childs, Jobs, Numbers, etc.

Selecting Specific Fields

Use $ to specify which fields you want:

// Only get Car information
DocsWhere("Users", "{'Name':'Bob'}")
    .Select<UserInfo>("{'Car':{'Engine':$,'Model':$}}");
    // Result: Returns only Car data
    // { Car: { Engine: "2", Model: "Subaru" } }
    // Note: After updates, Engine will have value "2"

Extracting Nested Data

Use ! to extract nested objects directly:

// Get just the Car object as the result
DocsWhere("Users", "{'Name':'Bob'}")
    .Select("{'Car':!{'Engine':$}}");

Selecting Array Items

Get specific items from arrays:

// Get the second number (index 1) from Bob
DocsWhere("Users", "{'Name':'Bob'}")
    .Select<UserInfo>("{'Numbers':[@1,$]}");
    // Result: Returns [2] (Bob's second number after updates)

// Get the last number
DocsWhere("Users", "{'Name':'Bob'}")
    .Select<UserInfo>("{'Numbers':[Last,$]}");
    // Result: Returns [3] (Bob's last number)

Getting a Range of Array Items

Use P (Portion) to get a slice:

// Get items from index 0 to 2 (first 2 items)
DocsWhere("Users", "{'Name':'Bob'}")
    .Select<UserInfo>("{'Numbers':[P,0,2,$]}");

Filtering and Selecting Arrays

Combine filtering with the R (Result) operator:

// Get only Manager jobs
DocsWhere("Users", "{'Name':'Bob','Jobs':[{'Position':'Manager'}]}")
    .Select<UserInfo>("{'Jobs':[R,{'Company':$,'Position':$}]}");
    // Result: Returns only Bob's Manager job
    // Jobs: [{ Company: "Amazon", Position: "Manager" }]
    // (Microsoft Engineer job is filtered out)
💡 Tip: The R operator returns only array items that matched the filter, rather than the entire array.

Aggregate Functions

// Count documents
DocsWhere("Users", "{'Name':'Bob'}")
    .Count();
// Result: 1

// Sum values in an array
DocsWhere("Users", "{'Name':'Bob'}")
    .Sum("{'Numbers':[$]}");
// Result: 6 (sum of Bob's Numbers after updates: [1,2,3])

8. Deleting Data

Deleting Fields

To remove a field from a document:

// Delete the Age field
DocsWhere("Users", "{'Name':'Bob'}")
    .Delete("{'Age':$}");

Deleting Nested Fields

To remove fields from nested objects:

// Delete only the Engine from Car
ModifyDocsWhere("Users", "{'Name':'Bob'}")
    .Delete("{'Car':{'Engine':$}}");

Deleting Array Items by Index

// Delete the second item (index 1)
ModifyDocsWhere("Users", "{'Name':'Bob'}")
    .Delete("{'Numbers':[@1,$]}");

// Delete the last item
ModifyDocsWhere("Users", "{'Name':'Bob'}")
    .Delete("{'Numbers':[Last,$]}");

Deleting Specific Values from Arrays

Remove items that match a specific value:

// Delete the number 4 from the array
DocsWhere("Users", "{'Name':'Bob'}")
    .Delete("{'Numbers':[4]}");

Deleting Objects from Arrays

Remove objects that match certain criteria:

// Delete all jobs at Google
ModifyDocsWhere("Users", "{'Name':'Bob','Jobs':[{'Company':'Google'}]}")
    .Delete("{'Jobs':[$]}");
⚠️ Warning: When deleting from arrays with filters, all matching items will be removed. Make sure your filter is specific enough!

9. Advanced Operations

Skip and Take (Pagination)

To get a specific subset of results:

// Skip first document, take 1
DocsOf("Users")
    .Skip(1)
    .Take(1)
    .Count();
💡 Tip: Skip and Take are useful for pagination. For example, page 1 might Skip(0).Take(10), page 2 Skip(10).Take(10), etc.

Sorting Results

Use OrderBy to sort your results:

// Sort by Name ascending (A to Z)
DocsOf("Users")
    .OrderBy("{'Name':$}")
    .Select<UserInfo>();

// Sort by Name descending (Z to A)
DocsOf("Users")
    .OrderByDesc("{'Name':$}")
    .Select<UserInfo>();

Complex AND/OR Combinations

You can nest AND/OR conditions:

// (IsManager = true) AND (Weight = 70 OR Weight = 75)
DocsWhere("Users", "{'IsManager':true}")
    .AndWhere(
        DocsWhere("Users", "{'Weight':70}")
            .OrWhere("{'Weight':75}")
    )
    .Select<UserInfo>();
    // Result: Returns Ted (Weight: 75, IsManager: true)

Faceted Search

Search using multiple categories (facets):

// Find users matching both facet filters
DocsWhereFacet("Users", "{'Facet1':{'Attr1':true,'Attr2':true},'Facet2':{'Attr1':true,'Attr2':true}}")
    .Select<UserInfo>();
    // Result: Returns Bob (when both Attr1 and Attr2 are true in facets)

// Find with correct facet values
DocsWhereFacet("Users", "{'Facet1':{'Attr2':true},'Facet2':{'Attr2':true}}")
    .Select<UserInfo>();
    // Result: Returns Bob (both facets have Attr2: true)
💡 Tip: Faceted search is useful for e-commerce filters where users can select multiple categories (like size, color, brand).

Extracting Collections

Convert query results into a new collection:

// Extract all child objects into a new collection
var coll = DocsWhere("Users", "{'Name':'Bob'}")
    .ToCollection("{'Childs':[!{'Name':$,'Age':$}]}");

Checking Existence

Use Any() to check if documents exist:

// Returns true if any matching documents exist
uint docID = 1;
var exists = DocsWhere("Users", docID)
    .AndWhere("{'Jobs':[{'Company':'Microsoft'}]}")
    .Any();

Working with Variables

You can pass objects as variables using @VariableName:

ModifyDocsWhere("Users", "{'Name':'Tim'}")
    .Update("{'Participants':[Add,@Participant]}",
    new Participant {
        Name = "Hello",
        Gender = GenderType.Boy,
        Questions = new List<string> { "Q1", "Q2" }
    });

Chaining Operations

You can chain multiple operations together:

// Complex query chain
DocsWhere("Users", "{'Name':'Bob'}")
    .AndWhere("{'Age':20}")
    .OrWhere("{'Name':'Tim'}")
    .OrderBy("{'Name':$}")
    .Skip(0)
    .Take(10)
    .Select<UserInfo>();