How To Add JSON Objects In Backendless Database

Backendless Database now supports the JSON data type, a significant innovation that allows us to support both structured and unstructured data.

Before we get into the details of this new release, let’s discuss why the addition of JSON support is so significant.

Previously, we primarily supported structured data; to put it simply, structured data is data that matches up nicely with the columns in your database. We also have data validators to help you ensure that only the proper data type is stored in a field.

With the introduction of JSON support, you are now able to store unstructured data in JSON format in your database. What is unstructured data? Here’s a great description from Western Digital:

Unstructured data is data that doesn’t fit in a spreadsheet with rows and columns. It isn’t [typically] in a database. It’s not in an ERP or CRM type data where you know what kind of data is in each cell, and how it relates to the rest of the data. Unstructured data is somewhat renegade – things that exist just because they exist.

Examples of unstructured data includes things like video, audio or image files, as well as log files, sensor or social media posts. … Now add to that all the machine to machine and sensor data flowing out of the Internet of Things, and you start to understand the magnitude of the challenge.

JSON support in Backendless Database is designed to help you meet that challenge. This new feature unlocks a world of possibilities by putting that data in your database in a way that’s queryable

In this article, we’ll dive a little deeper into the nuts and bolts of what you can do with this new feature.

You can read the JSON Data documentation for REST API here. All of our APIs and SDKs now support JSON data.

Creating A JSON Data Column

We make it as easy as possible to update your Backendless database schema in our visual editor. The first thing we’ll do is show you how easy it is to add a JSON-type data column to a table.

Simply click on the schema editor and select the JSON data type.

JSON objects will be stored in the new column in the form they are received from the client – objects, arrays, etc.

Once the JSON data is stored in the database, you can use the Database API to retrieve, update, and delete it just like any other data object. You can also use the REST console and SQL-driven search on the column.

Querying JSON In Your Database

This is where things get fun!

We have in-depth documentation on querying JSON, so we’ll keep this to a high-level overview.

We have made it possible to run queries against JSON data stored in your database. This feature is available on all Backendless SDKs as well as REST API. To demonstrate, we’ll be using the REST Console in Backendless.

A JSON query is a special syntax for referencing elements of stored JSON documents. The syntax can be used in “where clause” queries to retrieve database objects that contain JSON documents matching the query. Additionally, you can retrieve elements of JSON as individual properties of the returned objects. This section of the guide describes both of these capabilities.

JSON and Where Clause

Backendless Database supports the querying of the stored JSON data using a special format for addressing individual JSON elements. Using the format, you can create “where clause” statements with conditions that identify the objects the database returns for the query.

To understand how to format the where clause conditions for JSON documents, consider the example below. Suppose the database stores the following JSON documents in a database column called profile:

Object 1:

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

Object 2:

{
    "name" : "Bob",
    "age" : 55,
    "address" : {
      "street" : "555 Commerce St",
      "city" : "Chicago",
      "state": "Illinois"
    },
    "favoriteNumbers": [ 21,100,200,300 ],
    "favoriteColors": [ "Blue", "Red" ]
}

The objects would appear in Backendless Console as shown below:

To create a condition that references a JSON document element, use the following format in a where clause query:

jsonColumnName->'$.jsonElementName' condition

For example, the following where clause query retrieves all Person objects where the JSON documents in the profile column have age greater than 30:

profile->'$.age' > 30

Since the sample JSON documents shown above have a nested address object, your query can reference the elements of that JSON object as well. For instance, the query below retrieves all Person objects that have city in address set to 'New York'. Notice that the address element contains string values, as a result, the value to compare with must be enclosed in single quotes ('New York', not New York)

profile->'$.address.city' = 'New York'

Multiple conditions for JSON elements can be combined in the same where clause query. For example, the following query will retrieve all Person objects where age is greater than 25 and the city is 'Chicago':

profile->'$.age' > 25 and profile->'$.address.city' = 'Chicago'

The query format allows to reference elements of JSON arrays. Sample JSON documents in the example above have two arrays: favoriteNumbers and favoriteColors. To create a condition referencing an array, use the following format:

jsonColumnName->'$.jsonArrayElement[ index ]' condition

For example, the following where clause can be used to retrieve all Person objects where the first favoriteNumber is 21:

profile->'$.favoriteNumbers[ 0 ]' = 21

JSON in Response Properties

Backendless data retrieval API supports retrieval of individual properties/columns from your data tables. With the JSON support, the API has been expanded to enable “extraction” of individual JSON object elements, a group of elements, array items as individual response properties. Using the sample JSON document shown above, the following API retrieves city from the address object and the person’s name:

https://api.backendless.com/<application-id>/<REST-api-key>/data/Person?property=profile->'$.name' as name&property=profile->'$.address.city' as city

For the request shown above, the server returns the following response: As you can see the response contains the name and city properties – the values in the properties were obtained from the JSON document associated with each corresponding Person object:

[
  {
    "name": "Bob",
    "___class": "Person",
    "city": "Chicago",
    "objectId": "1316FEE4-92A6-4400-9D08-EB243BF57671"
  },
  {
    "name": "Joe",
    "___class": "Person",
    "city": "New York",
    "objectId": "DE7EE84B-859D-4148-A425-206E6104DB12"
  }
]

Read about Query Functions and more JSON examples in the documentation.

Summary

We can’t wait to see what you do with JSON support. This addition opens countless new avenues for app development.

There’s plenty more to come! We are hard at work innovating to ensure Backendless is the best platform for app builders. We have many more new features in the works that we can’t wait for you to see.

Be among the first to know about new releases by following us on Twitter, GitHub, and LinkedIn, and give us an upvote on ProductHunt!

Happy Codeless Coding!