C3 AI Documentation Home

Fetch and Filter Data from the Database Using JavaScript

After you turn a Type into an Entity Type (also known as Persistable), you can persist data in a database for later retrieval. Learn how to create, update, and delete data from the database.

This topic covers how to fetch data from the database using the JavaScript SDK. You can also learn how to fetch data using the Python SDK.

Data modeling

Assume you have defined a type that tracks information about smart bulbs, such as the smart bulb's bulbType, manufacturer and essential metrics on the bulb:

Type
entity type SmartBulb schema name "SMRT_BLB" {
    // the type of lighting technology employed by the bulb—like INCAN (incandescent), LED (light-emitting diode), or
    // CFL (compact fluorescent lamp)—affects its efficiency, lifespan, and other characteristics.
    bulbType: !string enum('LED', 'INCAN', 'CFL')
    
    // the smart bulb's manufacturer
    manufacturer: string
    
    // essential metrics that provide information about the bulb's power consumption and light output
    wattage: decimal
    lumensUOM: decimal
    lumensPerWatt: decimal stored calc "lumensUOM / wattage"
}

Calling the FetchSpec#fetch method with the parameter count:true on the SmartBulb type returns 0 since the type is not hydrated with data. If the type does contain data, call removeAll(null, true) on the type to remove any data it contains.

JavaScript
// Clear the type of any persisted data
SmartBulb.removeAll(null, true);

You can use createBatch() to perform operations on several objects at the same time. Using batch operations allows the C3 Agentic AI Platform to optimize database read/writes.

Add several smart bulbs to the database:

JavaScript
// Create in-memory data about bulbs
var bulbs = [
    {id: "bulb-10", bulbType: "LED", manufacturer: "GE", wattage: 17, lumensUOM: 1200 }, 
    {id: "bulb-20", bulbType: "INCAN", manufacturer: "Philips", wattage: 14, lumensUOM: 1150 }, 
    {id: "bulb-30", bulbType: "LED", manufacturer: "Bell", wattage: 23, lumensUOM: 1370 }
];
SmartBulb.createBatch(bulbs);

More information about stored calc can be found in the Fields and Methods topic.

Find all instances that match a criteria

Another common use-case is to find all instances that match a certain criteria. Sometimes you want to only display the list to the user, other times you want to do some data manipulation. The first step is to fetch data from the database.

If you know the unique ID of an instance, you can fetch it from the database using Persistable#fetch:

JavaScript
var bulb = SmartBulb.fetch({
    filter: "id == 'bulb-10'"
});

If you do not know the ID and you want to apply more granular control over the query pass a FetchSpec to Persistable#fetch to control filter, fields, sorting, and limits::

JavaScript
// Fetch Philips bulbs with specific fields, sorted by id (desc), bounded result set
var philipsBulbs = SmartBulb.fetch({
  filter: Filter.eq("manufacturer", "Philips"),
  include: "[id, bulbType, manufacturer]",
  order: "descending(id)",
  limit: 500   // pick a sensible page size; default is 2000 if omitted
});

With the filter parameter, you can filter data using comparison operators, including the use of expression engine functions. View the FetchFilterSpec#filter Type to learn more.

JavaScript
SmartBulb.fetch({
    // Filter criteria
    filter: "bulbType == 'LED' && wattage > 10",
    // Which field to return
    include: "[id, wattage]",
    // How to sort results
    order: "wattage, descending(id)",
    // Maximum results to return
    limit: 10
});

The filter criteria can get quite complex. Notice when filtering for LED smart bulbs, you must use quotes around the string "LED". To prevent mistakes, and keep things organized, use the Filter Type:

JavaScript
// Similar query, but this time using the Filter type to prevent mistakes
SmartBulb.fetch({
    filter: Filter.eq("bulbType", "LED").and().gt("wattage", 10),
    include: "[id, wattage]",
    order: "wattage, descending(id)",
    limit: 10
});

Check if any instances match criteria

Sometimes you just want to check if there are any instances that match a given criteria without fetching the instances from the database. Use Persistable#exists to get a boolean or Persistable#fetchCount to get how many instances match the filtering criteria:

JavaScript
// Check if there's at least one smart bulb that complies with the criteria
SmartBulb.exists({
    filter: Filter.eq("bulbType", "LED").and().gt("wattage", 10)
});

Use these methods when you do not need the actual results, since they are faster and require less memory to run.

Find instances and get a result stream

Since .fetch() returns all the results at the same, as the size of your dataset increases, fetching data requires increasingly more memory. Minimize the footprint of your applications by using the Persistable#fetchObjStream method instead:

JavaScript
// Query data and stream results
var allBulbs = SmartBulb.fetchObjStream({
    filter: Filter.eq("bulbType", "LED")
});

var combinedLedWattage = 0;

// Consume stream, and close stream on error
allBulbs.each(function(bulb) {
    combinedLedWattage = combinedLedWattage + bulb.wattage
});

The .fetchObjStream() method streams the results as you iterate them, thus keeping the memory footprint lower.

Fetch and transform data

The .fetch() method allows you to query data and filter by relevant criteria, but it doesn't offer a way to transform the results. To transform data, you have to iterate through the results and process them with your own logic.

If you need to query and transform the data, use the Persistable#evaluate method:

JavaScript
var efficientLedBulbs = SmartBulb.evaluate({
    // Filter criteria, optional
    filter: "bulbType == 'LED' && wattage > 10.1",
    // For each instance that matches the criteria, return
    // ID of the instance
    // manufacturer name in upper case
    // wattage, rounded to the nearest integer
    projection: "id, upperCase(manufacturer), round(wattage)"
});

The .evaluate() method not only allows you to fetch and filter persisted data, it also allows transforming data using expression engine functions.

Aggregate data

The real power of .evaluate() becomes evident when you aggregate data and compute results using the aggregate data:

JavaScript
var manufacturerSummary = SmartBulb.evaluate({
    // Filter criteria
    filter: "bulbType == 'LED' && wattage > 10",
    // Aggregate data by manufacturer
    group: "manufacturer",
    // Return manufacturer and aggregate data about it
    projection: "manufacturer, max(wattage)",
    // Filter out all manufacturers with lower average efficiency
    having: "avg(wattage) > 5"
});

// The return value is an object with the results, and some metadata about the result set
console.log("There are " + manufacturerSummary.count + " manufacturers with efficient LED bulbs");

// And the actual results are accessible in the .tuples field
manufacturerSummary.tuples.length

To dive deeper into using the fetching data and using the FetchSpec Type, see Fetch and Query Multiple Types.

Related content

Was this page helpful?