Skip to content

JSON Query

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

two-json-objects

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:

DataQueryBuilder queryBuilder = DataQueryBuilder.Create();
queryBuilder.AddProperty( "profile->'$.name' as name" );
queryBuilder.AddProperty( "profile->'$.address.city' as city" );
Backendless.Data.Of( "Person" ).Find( queryBuilder );

For the request shown above, the server returns the following response:(showing in the REST format for simplicity. For the SDK you use, the response will include objects with the same properties as below) 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"
  }
]

JSON Query Functions

There are several special functions which can be used both in where clause and response properties. These functions enable additional data retrieval and JSON data conversion capabilities. All functions described below allow for at least one path argument and some (such as JSONSEARCH) return path.

JSONCONTAINS

JSONCONTAINS( target, candidate[, path] )

Indicates by returning 1 or 0 whether a given candidate JSON document is contained within a target JSON document, or if the path argument is specified, whether the candidate is found at the specific path within the target. The target argument can be either the name of a column of JSON type or JSON Path. Returns NULL if any argument is NULL, or if the path argument does not identify a section of the target document. An error occurs if target or candidate is not a valid JSON document, or if the path argument is not a valid path expression or contains a * or ** wildcard. Both target and candidate parameters can reference a JSON column or contain literal JSON values. See the section below for examples.

JSONCONTAINSPATH

JSONCONTAINSPATH( jsondocument, one_or_all, path[, path]... )

Returns 0 or 1 to indicate whether a JSON document represented by the jsondocument argument contains data at a given path or paths. The jsondocument argument can be either the name of a column of JSON type or JSON Path. Returns NULL if any argument is NULL. An error occurs if the jsondocument argument is not a valid JSON document, any path argument is not a valid path expression, or one_or_all is not 'one' or 'all'.

To check for a specific value at a path, use JSONCONTAINS() instead.

The return value is 0 if no specified path exists within the document. Otherwise, the return value depends on the one_or_all argument, which must be one of the following values:

  • 'one': 1 if at least one path exists within the document, 0 otherwise.
  • 'all': 1 if all paths exist within the document, 0 otherwise.

JSONSEARCH

JSONSEARCH( jsondocument, one_or_all, searchString[, escapeChar[, path]...], caseInsensitive )

Returns the path to the given string within a JSON document. The jsondocument argument can be either the name of a column of JSON type or JSON Path. Returns NULL if any of the jsondocument, searchString, or path arguments are NULL; no path exists within the document; or searchString is not found. An error occurs if the jsondocument argument is not a valid JSON document, any path argument is not a valid path expression, one_or_all is not 'one' or 'all', or escapeChar is not a constant expression.

The one_or_all argument must be one of the following values and affects the search as follows:

  • 'one': The search terminates after the first match and returns one path string. It is undefined which match is considered first.
  • 'all': The search returns all matching path strings such that no duplicate paths are included. If there are multiple strings, they are autowrapped as an array. The order of the array elements is undefined.

Within the searchString argument, the % and _ characters work as for the LIKE operator: % matches any number of characters (including zero characters), and _ matches exactly one character.

To specify a literal % or _ character in searchString, precede it by the escape character. The default is \ if the escapeChar argument is missing or NULL. Otherwise, escapeChar must be a constant that is empty or one character.

By setting the caseInsensitive argument to true you can force the operation to ignore the case of a string value that you search for. The default value of the caseInsensitive argument is false, which means that the search operation always considers the case of a string value.

CAST

CAST( expression AS type )

The function converts the expression to the specified type. The expression argument can be a scalar value, name of a database column, or a reference to a JSON value using the columnName->'jsonPath' format. The type argument must be one of the following values:

  • JSON
  • BINARY
  • CHAR
  • DATE
  • DATETIME
  • TIME
  • SIGNED
  • UNSIGNED
  • FLOAT
  • DOUBLE

JSONKEYS

JSONKEYS( jsonDocument[, path] )

Returns the keys from the document/object identified by jsonDocument and optionally the path arguments. The jsonDocument argument can be either the name of a column of JSON type or JSON Path. If the path argument is provided, the function returns top-level keys from the selected path for jsonDocument. Returns NULL if any argument is NULL, the jsonDocument argument is not an object, or path, if given, does not locate an object. An error occurs if the jsonDocument argument is not a valid JSON document or the path argument is not a valid path expression or contains a * or ** wildcard. The result array is empty if the selected object is empty. If the top-level value has nested subobjects, the return value does not include keys from those subobjects.

Usage examples:

JSONKEYS( profile )  
JSONKEYS( profile->'$.address' )  
JSONKEYS( profile, '$.address' )

Examples

Search with Where Clause

Consider a database containing information about books. The database table has a JSON column called bookinfo the following database records:

bookinfo-records

Individual JSON objects in the bookinfo column have the following structure:

{
  "ISBN": "1538718871",
  "pages": 336,
  "title": "The midwife murders",
  "author": "James Patterson",
  "prices": [
    {
      "hardcover": 21.29
    },
    {
      "kindle": 11.99
    },
    {
      "audiobook": 20.76
    },
    {
      "paperback": 14.38
    }
  ],
  "language": "English",
  "amazonURL": "https://www.amazon.com/dp/1538718871",
  "publisher": "G.P. Putnam's Sons (August 11, 2020)"
}

The examples below demonstrate the usage of the JSON Query and Query Functions to retrieve data:

**Get objects where a JSON element contains a string. **

The where clause below can be used to get all book objects where the publisher element contains "G.P Putnam" string. Notice the string to match is defined as a pattern using the % character. That matches any string that starts with the specified value:

jsonsearch( bookinfo->'$.publisher', 'one', 'G.P. Putnam%') is not null

Alternatively, the first argument in the jsonsearch function can be the entire JSON document referenced through the column name:

jsonsearch( bookinfo, 'one', 'G.P. Putnam%') is not null

Codeless Reference

data_json_query_1

Important

For a detailed description of all input parameters see the Basic Object Retrieval topic of this guide.

Get objects where array contains an object with the property.

The where clause below returns all book objects that contain a price for the hardcover edition of the book:

jsoncontainspath(bookinfo, 'one', '$.prices[*].hardcover') = 1

Codeless Reference

data_json_query_2

Get objects for a condition for a numeric element.

The where clause below finds and returns all book objects that have more 380 pages in the pages element of the stored JSON object:

bookinfo->'$.pages' > 380

Codeless Reference

data_json_query_3

Get objects for a condition for an array element.

The where clause below finds and returns all book objects where the price for the kindle edition of the book is greater than 14:

bookinfo->'$.prices[*].kindle' > cast('[14]' as json)

Codeless Reference

data_json_query_4

Search in Arrays

The JSON objects in the example above include an array that contains other JSON objects. In cases when a JSON array contains literal values such as numbers or strings (as opposed to objects), you can use JSON Query functions to perform array searches. Consider the following example:

lottery-table-results

The Lottery table contains results of lottery drawings. The results are stored as a JSON array in the results column. Consider the examples below demonstrating how JSON query functions can be used with arrays:

Search for a specific number in JSON arrays.

The where clause below retrieves all lottery results containing number 10. Notice the second argument ('10') is enclosed into single quotes. This is for the reason that the argument must be a valid JSON value. If you were to search for a string in a JSON array the argument would need to be formatted as '"valueToSearch"' :

jsoncontains( results, '10' ) = 1

Codeless Reference

data_json_query_5

Search for multiple numbers in JSON arrays.

The where clause below retrieves all lottery results containing both numbers 1 and 8. The order of the numbers in the database and in the search argument is irrelevant. Backendless database will be able to match them in any order:

jsoncontains( results, '[1, 8]' ) = 1

data_json_query_6