Search JSON Data in Cache using SQL Queries

Note

This feature is available in NCache Enterprise edition only.

You can query cache data as JSON if the added data has searchable attributes associated with it. The searchable attributes include:

Tags
NamedTags
Groups

Pre-Requisites

Include the following namespaces in your application:
- Alachisoft.NCache.Client
- Alachisoft.NCache.Runtime.JSON
- Alachisoft.NCache.Runtime.Exceptions
Cache must be JSON Serialized through NCache Web Manager.
The application must be connected to cache before performing the operation.
Cache must be running.
To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Add JsonObject with Tags

You can add JsonObject in your cache as a CacheItem. CacheItem is a custom class provided by NCache which can be used to add data to the cache. CacheItem lets you add various properties to it e.g Locking, Dependency, Tags, NamedTags and Groups.

Warning

An attribute cannot contain a reference to the object itself. If done so, an exception is thrown.

The following example adds a CacheItem to the cache with JsonObject product as the value of the cacheItem. Tags are added to the cacheItem and then item is fetched using the tag using a query command.

try
{
    // Pre-Condition: Cache is already connected
    // Cache is JSON serialized

    // Get product from database against given product ID
    Product product = FetchProductFromDB(1001);

    // Create a unique key for the object
    string key = $"Product:{product.ProductID}";

    // Create a new JSON object and set attributes
    // string values need to be added with JsonValue
    var jsonProduct = new JsonObject();
    jsonProduct.AddAttribute("ProductID", product.ProductID);
    jsonProduct.AddAttribute("ProductName", (JsonValue)product.ProductName);
    jsonProduct.AddAttribute("Category", (JsonValue)product.Category);
    jsonProduct.AddAttribute("UnitPrice", product.UnitPrice);
    jsonProduct.AddAttribute("UnitsInStock", product.UnitsInStock);

    // Create a new CacheItem
    var item = new CacheItem(jsonProduct);

    // Add Tag to the cacheItem
    item.Tags = new Tag[] { new Tag("FoodItems") };

    // Add cacheItem in the cache with the value
    cache.Insert(key, item);

    // Retrieve the cacheItem using the tag with a query
    string query = "Select * FROM Alachisoft.NCache.Runtime.JSON.JsonObject WHERE $Tag$ = ?";
    var queryCommand = new QueryCommand(query);
    queryCommand.Parameters.Add("$Tag$", "FoodItems");
    var queryResult = cache.SearchService.ExecuteReader(queryCommand);

    // QueryResult contains all the keys and metadata of result
    if (queryResult.FieldCount > 0)
    {
        while (queryResult.Read())
        {
            // Perform operation according to your logic
        }
    }
    else
    {
        // No data containing the named tag(s) exist
    }
}
catch (OperationFailedException ex)
{
    if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
    {
        // Make sure that the query format is correct
    }
    else if (ex.ErrorCode == NCacheErrorCodes.ATTRIBUTE_ALREADY_EXISTS)
    {
        // An attribute already exists with the same name
    }
    else if (ex.ErrorCode == NCacheErrorCodes.REFERENCE_TO_SELF)
    {
        // No object can contain a reference to itself
    }
    else
    {
        // Exception can occur due to:
        // Connection Failures 
        // Operation Timeout
        // Operation performed during state transfer
    }
}
catch (Exception ex)
{
    // Any generic exception like ArgumentNullException or ArgumentException
}

Recommendation: To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Add JsonArray with Named Tags

You can also add JsonArray in a cache as CacheItem. CacheItem is a custom class provided by NCache which can be used to add data to the cache. CacheItem lets you add various properties to it e.g Locking, Dependency, Tags, NamedTags and Groups.

The following example adds JsonArray array as a CacheItem and adds a NamedTag with the array and then later the item is fetched using the NamedTag.

try
{
    // Pre-Condition: Cache is already connected
    // Cache is JSON serialized

    // Create a new JsonArray
    var jsonArray = new JsonArray();

    // Get top ten proudcts from the data source
    Products[] products = FetchTop10Products();

    // Convert these products in JsonObjects
    foreach (Products product in products)
    {
        // Create jsonObject and set its attributes
        // ProductName is string so it needs to be added with JsonValue
        var jsonProduct = new JsonObject();
        jsonProduct.AddAttribute("ProductID", product.ProductID);
        jsonProduct.AddAttribute("ProductName", (JsonValue)product.ProductName);
        jsonProduct.AddAttribute("Category", (JsonValue)product.Category);
        jsonProduct.AddAttribute("UnitPrice", product.UnitPrice);
        jsonProduct.AddAttribute("UnitsInStock", product.UnitsInStock);

        // Add jsonObjects to the jsonArray
        jsonArray.Add(jsonProduct);
    }

    // Create a unique key for the array
    string key = "Products";
    // Create a new CacheItem
    var item = new CacheItem(jsonArray);

    // Add NameTags to the NamedTagsDictionary to be added to the cacheItem
    var productNamedTag = new NamedTagsDictionary();

    // Add NamedTag 'Discount' to the dictionary with the value
    productNamedTag.Add("Discount", 0.5);
    item.NamedTags = productNamedTag;
    // Add CacheItem with the NamedTag associated
    cache.Insert(key, item);

    // Create a query for fetching item with NamedTag
    string query = "Select * FROM Alachisoft.NCache.Runtime.JSON.JsonArray WHERE Discount = ?";

    QueryCommand queryCommand = new QueryCommand(query);
    queryCommand.Parameters.Add("Discount", 0.5);
    var queryResult = cache.SearchService.ExecuteReader(queryCommand);

    // QueryResult contains all the keys and metadata of result
    if (queryResult.FieldCount > 0)
    {
        while (queryResult.Read())
        {
            // Perform operation according to your logic
        }
    }
    else
    {
        // No data containing the named tag(s) exist
    }
}
catch (OperationFailedException ex)
{
    if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
    {
        // Make sure that the query format is correct
    }
    else if (ex.ErrorCode == NCacheErrorCodes.ATTRIBUTE_ALREADY_EXISTS)
    {
        // An attribute already exists with the same name
    }
    else if (ex.ErrorCode == NCacheErrorCodes.REFERENCE_TO_SELF)
    {
        // No object can contain a reference to itself
    }
    else
    {
        // Exception can occur due to:
        // Connection Failures 
        // Operation Timeout
        // Operation performed during state transfer
    }
}
catch (Exception ex)
{
    // Any generic exception like ArgumentNullException or ArgumentException
}

Recommendation: To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Add JsonValue with Group

You can also add JsonValue in a cache as CacheItem. CacheItem is a custom class provided by NCache which can be used to add data to the cache. CacheItem lets you add various properties to it e.g Locking, Dependency, Tags, NamedTags and Groups.

The following example adds a jsonValue to the cache as a cacheItem and then fetches the value by group using query.

try
{
   // Pre-Condition: Cache is already connected
   // Cache is JSON serialized

   // Add a string value as JSON
   Product product = FetchProductFromDB(1001);
   var jsonValue = (JsonValue)product.ProductName;

   // Create a unique key for this
   string key = $"Product:{product.ProductID}";

   // Create a new cacheitem and add jsonValue in it
   var item = new CacheItem(jsonValue);

   // Assign group to the jsonValue
   item.Group = "Eatables";

   // Add the cacheItem in the cache
   cache.Insert(key, item);

   // Create a query for fetching item with Group
   string query = "Select * FROM Alachisoft.NCache.Runtime.JSON.JsonValue WHERE $Group$ = ?";
   var queryCommand = new QueryCommand(query);
   queryCommand.Parameters.Add("$Group$", "Eatables");
   var queryResult = cache.SearchService.ExecuteReader(queryCommand);

   // QueryResult contains all the keys and metadata of result
   if (queryResult.FieldCount > 0)
   {
       while (queryResult.Read())
       {
           // Perform operations according to your business logic
       }
   }
   else
   {
       // No data containing the named tag(s) exist
   }
}
catch (OperationFailedException ex)
{
   if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
   {
       // Make sure that the query format is correct
   }
   else
   {
       // Exception can occur due to:
       // Connection Failures 
       // Operation Timeout
       // Operation performed during state transfer
   }
}
catch (Exception ex)
{
   // Any generic exception like ArgumentNullException or ArgumentException
}

Recommendation: To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Additional Resources

NCache provides sample application for Cache Data as JSON at:

GitHub
Shipped with NCache: *%NCHOME%\samples\dotnet\BasicOperationsWithJSON

Search JSON Data in Cache using SQL Queries

Note

Pre-Requisites

Add JsonObject with Tags

Warning

Add JsonArray with Named Tags

Add JsonValue with Group

Additional Resources

See Also