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:

Request/Endpoint:

POST https://api.backendless.com/APP-ID/REST-API-KEY/data/Person 

Request header:

Content-Type:application/json

Request body:

{
  "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" ]
  }
}

Response:

{
  "___class": "Person",
  "ownerId": null,
  "updated": null,
  "created": 1597165292000,
  "objectId": "553C19C2-988D-47FA-9BF2-EC4AE6C91CFE",
  "profile": {
    "age": 34,
    "name": "Joe",
    "address": {
      "city": "New York",
      "state": "New York",
      "street": "123 Main St"
    },
    "favoriteColors": [ "Blue", "Green" ],
    "favoriteNumbers": [ 5,7,9,21,100 ]
  }
}

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:

Request/Endpoint:

PUT https://api.backendless.com/APP-ID/REST-API-KEY/data/Person/OBJECT_ID 

Request header:

Content-Type:application/json

Request body:

{
  "profile" : {
    "___operation" : "JSON_SET",
    "args" : {
      "$.address.street" : "555 Freedom Ave"
    }
  }
}

Response:

{
  "___class": "Person",
  "ownerId": null,
  "updated": 1597247133000,
  "created": 1597165292000,
  "objectId": "553C19C2-988D-47FA-9BF2-EC4AE6C91CFE",
  "profile": {
    "age": 34,
    "name": "Joe",
    "address": {
      "city": "New York",
      "state": "New York",
      "street": "123 Main St"
    },
    "favoriteColors": [ "Blue", "Green" ],
    "favoriteNumbers": [ 5,7,9,21,100 ]
  }
}

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

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:

Request/Endpoint:

GET https://api.backendless.com/APP-ID/REST-API-KEY/data/Person

Request header:

none

Request body:

none

Response:

[
  {
    "___class": "Person",
    "ownerId": null,
    "updated": 1597247133000,
    "created": 1597165292000,
    "objectId": "553C19C2-988D-47FA-9BF2-EC4AE6C91CFE",
    "profile": {
      "age": 34,
      "name": "Joe",
      "address": {
        "city": "New York",
        "state": "New York",
        "street": "123 Main St"
      },
      "favoriteColors": [ "Blue", "Green" ],
      "favoriteNumbers": [ 5,7,9,21,100 ]
    }
  }
]

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

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'

Request/Endpoint:

the where clause part in the URL must be URL-encoded

GET https://api.backendless.com/APP-ID/REST-API-KEY/data/Person?where=profile-%3E'%24.age'%20%3E%2030%20and%20profile-%3E'%24.address.city'%20%3D%20'New%20York'

Request header:

none

Request body:

none

Response:

[
  {
    "___class": "Person",
    "ownerId": null,
    "updated": 1597247133000,
    "created": 1597165292000,
    "objectId": "553C19C2-988D-47FA-9BF2-EC4AE6C91CFE",
    "profile": {
      "age": 34,
      "name": "Joe",
      "address": {
        "city": "New York",
        "state": "New York",
        "street": "123 Main St"
      },
      "favoriteColors": [ "Blue", "Green" ],
      "favoriteNumbers": [ 5,7,9,21,100 ]
    }
  }
]

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