JSON Data in Database

Backendless database provides complete support for the JSON data type. This means database records/objects stored in a Backendless data table may be completely schema-less. JSON documents must be stored in a data column of type JSON. Column must be declared manually in Backendless Console - the Dynamic Schema Definition option does not detect and create JSON columns dynamically. A data table may contain multiple JSON columns.

Backendless supports a special format for addressing individual elements of JSON objects and arrays. The format can be used for partial updates, element deletion or value replacement. Additionally, the format can be used for the data retrieval purposes.

Backendless APIs include functionality supporting the following use-cases:

• Storing JSON documents in the database
• Retrieval of specific element(s) of stored JSON data
• Retrieval of complete JSON documents
• Retrieval of specific elements or entire JSON documents based on a query.
• Partial or full update of stored JSON documents
• Operations on JSON arrays - appending, inserting, replacing or removing array values

Consider the following example to understand how basic JSON operations work in Backendless. The example will work with a database table called Person. The table contains a column of type JSON called profile: For additional information about JSON columns, see the Declaring JSON Columns section of this guide.

Data stored in the profile column must be formatted either as a JSON object or a JSON array. There are no limitations on the structure of your JSON data, as long as it is valid, the root level JSON object or JSON array may contain other JSON objects or arrays. While this example demonstrates a data table with only one column of the JSON type, there is nothing that would prevent you from mixing data columns of other types with the JSON column(s). What makes the Backendless database even more powerful is the data retrieval queries can mix references to both JSON columns and the columns of scalar data types (STRING, TEXT, DATETIME, INT, DOUBLE, BOOLEAN, POINT, LINESTRING, POLYGON), you will see examples of this approach later in this guide.

Explaining JSON Format

Consider the following JSON document/object:

{
    "name" : "Joe",
    "age" : 34
}

The JSON document syntax can start either with curly brackets { } or square brackets [  ]; Any JSON document starts with a bracket facing to the right, for instance { or [, and ends with a bracket facing to the left, for example } or ]. Everything inside these brackets is considered as JSON content. The content structure consists of properties ( "name" , "age" ) and corresponding values ( "Joe" , 34 ).

When the JSON structure starts with square brackets, it represents an array. This array may contain at least one object, and this structure is very useful if you need to transfer or retrieve a few objects inside this array in a single request. Every object in the array must be separated with a comma except for the last one - This rule also applies to declaration of properties and  values:

[
    {
        "name" : "Joe",
        "age" : 34
    },
    {
        "name" : "Alice",
        "age" : 25
    }
]

The JSON format also supports nested objects and arrays, that simply represent objects or arrays assigned to a property inside the parent object. In the example below you can see that the property "address" contains a nested object with its own properties ( e.g. "street" , "city" )  and corresponding values ( e.g. "123 Main St" , "New York" ):

{
    "name" : "Joe",
    "age" : 34,
    "address" : {
      "street" : "123 Main St",
      "city" : "New York",
      "state": "New York"
    },
    "favoriteNumbers": [ 5,7,9,21,100 ],
    "favoriteColors": [ "Blue", "Green" ]
}

Furthermore, the JSON format supports assigning arrays to properties (e.g. "favoriteNumbers" , "favoriteColors" ), containing values of any JSON type.

Saving a JSON document in the database

The example below saves the JSON object in the Person table in the profile column using the following request:

Dictionary<String, Object> address = new Dictionary<String, Object>
{
  { "street", "123 Main St" },
  { "city", "New York" },
  { "state", "New York" }
};

List<Int32> favoriteNumbers = new List<Int32> { 5, 7, 9, 21, 100 };
List<String> favoriteColors = new List<String> { "Blue", "Green" };

Dictionary<String, Object> profile = new Dictionary<String, Object>
{
  { "name", "Joe" },
  { "age", 34 },
  { "address", address },
  { "favoriteNumbers", favoriteNumbers },
  { "favoriteColors", favoriteColors }
};

Dictionary<String, Object> person = new Dictionary<String, Object>
{
  { "profile", profile }
};

Backendless.Data.Of( "Person" ).Save( person, new AsyncCallback<Dictionary<String, Object>>(
  savedDictionary =>
  {
    System.Console.WriteLine( savedDictionary.ToString() );
  },
  fault =>
  {
    System.Console.WriteLine( fault.Message );
  } ) );

Codeless Reference

Updating a JSON document in the database

A JSON document stored in the Backendless database can be updated as a whole using the standard object update API. Additionally, Backendless supports a variation of the API to allow updated of individual JSON object properties and array values. The example shown below updates the "street" property of the "address" JSON object shown above:

Dictionary<String, Object> profile = JSONUpdateBuilder.SET()
                                                            .AddArgument( "$.address.street", "555 Freedom Ave" )
                                                            .Create();

      Dictionary<String, Object> person = new Dictionary<String, Object>
      {
        { "objectId", OBJECT_ID },
        { "profile", profile }
      };

      Backendless.Data.Of( "Person" ).Save( person, new AsyncCallback<Dictionary<String, Object>>(
      updatedObject=>
      {
        System.Console.WriteLine( updatedObject.ToString() )
      },
      fault=>
      {
        System.Console.WriteLine( fault.Message );
      } ));

For more information see the Updating JSON Data section of this guide.

Codeless Reference

Retrieving JSON data from the database

Retrieving JSON data works the same way as with any other data type in Backendless. Since JSON values are stored in a column/property of a data table, Backendless returns the JSON contents mapped to the property name. This is identical to the format used when saving JSON in the database:

Backendless.Data.Of( "Person" ).Find( new AsyncCallback<IList<Dictionary<string, object>>>(
      receivedObject=>
      {
        Dictionary<Object, Object> profile = (Dictionary<Object, Object>) receivedObject[ 0 ][ "profile" ];
      },
      fault=>
      {
        System.Console.WriteLine( fault.Message );
      }));

For more information see the Retrieving JSON Data and the JSON Query sections of this guide.

Codeless Reference

Running database queries for JSON data

The Backendless database supports a rich querying mechanism for JSON data. The API supports queries which retrieve database objects/records where the stored JSON documents contain certain values, or contain a range of values or even contain provided JSON documents. For a complete reference of available querying functions and examples, see the JSON Query section of this guide. The example below runs a query against the JSON document shown above. It retrieves all database objects where the "age" and  "address.city" elements of the stored JSON documents match the provided condition:

Query Condition (where clause):

profile->'$.age' > 30 and profile->'$.address.city' = 'New York'

DataQueryBuilder queryBuilder = DataQueryBuilder.Create()
                        .SetWhereClause( "profile->'$.age' > 30 and profile->'$.address.city' = 'New York'" );

      Backendless.Data.Of( "Person" ).Find( queryBuilder, new AsyncCallback<IList<Dictionary<String, Object>>>(
      response=>
      {
        System.Console.WriteLine( response.ToString() );
      },
      fault=>
      {
        System.Console.WriteLine( fault.Message );
      } ) );

For more information see the JSON Query section of this guide.

Codeless Reference