Skip to content

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.

profile-column

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.

Saving a JSON document in the database

Consider the following JSON document/object:

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

The object includes another object (the "address" property), a property containing an array of numbers ("favoriteNumbers") and a property containing an array of string values ("favoriteColors"). The JSON object will be saved in the Person table in the profile column using the following request:

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

[[Backendless.shared.data ofTable:@"Person"] saveWithEntity:@{@"profile": profile} responseHandler:^(NSDictionary *response) {
    NSLog(@"%@", response);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
let profile = ["name": "Joe",
               "age": 34,
               "address": ["street": "123 Main St",
                           "city" : "New York",
                           "state": "New York"],
               "favoriteNumbers": [5, 7, 9, 21, 100],
               "favoriteColors": ["Blue", "Green"]
    ] as [String : Any]

Backendless.shared.data.ofTable("Person").save(entity: ["profile": profile], responseHandler: { response in
    print(response)
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

Updating 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:

NSDictionary *profile = [[[JSONUpdateBuilder set]
                          addArgumentWithJsonPath:@"$.address.street" value:@"555 Freedom Ave"]
                        create];

[[Backendless.shared.data ofTable:@"Person"] saveWithEntity:@{@"objectId": OBJECT_ID, @"profile": profile} responseHandler:^(NSDictionary *response) {
    NSLog(@"%@", response);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
let profile = JSONUpdateBuilder.set()
    .addArgument(jsonPath: "$.address.street", value: "555 Freedom Ave")
    .create()

Backendless.shared.data.ofTable("Person").save(entity: ["objectId": OBJECT_ID, "profile": profile], responseHandler: { response in
    print(response)
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

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.shared.data ofTable:@"Person"] findWithResponseHandler:^(NSArray *response) {
    NSLog(@"%@", response);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
Backendless.shared.data.ofTable("Person").find(responseHandler: { response in
    print(response)
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

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 new];
queryBuilder.whereClause = @"profile->'$.age' > 30 and profile->'$.address.city' = 'New York'";

[[Backendless.shared.data ofTable:@"Person"] findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *response) {
    NSLog(@"%@", response);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
let queryBuilder = DataQueryBuilder()
queryBuilder.whereClause = "profile->'$.age' > 30 and profile->'$.address.city' = 'New York'"

Backendless.shared.data.ofTable("Person").find(queryBuilder: queryBuilder, responseHandler: { response in
    print(response)
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})