Skip to main content

​
JSON Functions Operating on JSON Data

These JSON functions provide means to process JSON documents stored in native JSON columns. This should be preferred over storing JSON data in TEXT columns.
  1. These operators extract a field from a JSON value, returing another JSON value:
  2. These functions convert a JSON value to a SQL value:
  3. These functions convert a SQL value to a JSON value:

​
JSON Functions Operating on TEXT Data

These JSON functions provide means to process JSON documents stored in TEXT columns. There are three types of JSON functions for extracting values:
  1. These functions extract part of the document, but preserve the original data in the result:
  2. These functions convert a JSON value to a SQL value:
  3. This function extracts a JSON value and converts it to a SQL value, combining the functionalities of the previous two types of functions:
The first two types of functions can be used in conjunction with each other to extract and convert JSON values to SQL values. The following code uses JSON_POINTER_EXTRACT to extract a value from a JSON document and JSON_VALUE to convert it into a SQL value: 
SELECT JSON_VALUE(JSON_POINTER_EXTRACT('{"key1":{"key3":"val1" }, "key2":"val2", "key3":5}', '/key1/key3'));
This statement returns val1 as a SQL TEXT value, rather than the original JSON value "val1" with the double quotes. The JSON_FORMAT function can be used to format JSON documents, including pretty-printing and minifying documents. Currently, JSON functions support only JSON pointer expressions, which provide a method for accessing specific elements within a JSON document. For convenience, the following aliases are available for JSON functions that use JSON pointer expressions:
  • JSON_POINTER_EXTRACT is an alias for JSON_EXTRACT with the path_syntax parameter set to 'JSONPointer'.
  • JSON_POINTER_EXTRACT_ARRAY is an alias for JSON_EXTRACT_ARRAY with the path_syntax parameter set to 'JSONPointer'.
Other than JSON functions, we also support some JSON operators.

​
JSON pointer expression syntax

The placeholder <json_pointer_expression> indicates where you should use a JSON pointer, which allows access to specific elements in a JSON document. For a formal specification, see RFC6901. A JSON pointer begins with a forward slash (/), indicating the root of the JSON document, followed by a sequence of property (key) names or zero-based ordinal numbers separated by slashes. Property names identify specific keys, while index numbers specify the Nth element in an array or object. The tilde (~) and forward slash (/) characters have special meanings and need to be escaped as follows:
  • To specify a literal tilde (~), use ~0.
  • To specify a literal forward slash (/), use ~1.
For example, consider the following JSON document: 
{
    "key": 123,
    "key~with~tilde": 2,
    "key/with/slash": 3,
    "value": {
      "dyid": 987,
      "keywords" : ["insanely","fast","analytics"]
    }
}
In the previous JSON document, the JSON pointer expressions evaluate to the following:
PointerResultNotes
/{
"key": 123,
"key~with~tilde": 2,
"key/with/slash": 3,
"value": {
"dyid": 987,
"keywords" : ["insanely","fast","analytics"]
}		Returns the whole document.
/key123
/key~0with~0tilde2Indicates the value associated with the key~with~tilde property name.
/key~1with~1slash3Indicates the value associated with the key/with/slash property name.
/0123Uses an ordinal to indicate the value associated with the key property name. The key property is in the first 0-based position.
/value/keywords/2analyticsIndicates the element “analytics”, which is in the third 0-based position of the array value associated with the keywords property.

​
JSON common example

The following JSON document, represented by the <json_common_example> placeholder, is used as a basis for all JSON function examples in this reference. 
{
    "key": 123,
    "value": {
      "dyid": 987,
      "uid": "987654",
      "keywords" : ["insanely","fast","analytics"],
      "tagIdToHits": {
        "map": {
          "1737729": 32,
          "1775582": 35
        }
      },
      "events":[
        {
            "EventId": 547,
            "EventProperties" :
            {
                "UserName":"John Doe",
                "Successful": true
            }
        },
        {
            "EventId": 548,
            "EventProperties" :
            {
                "ProductID":"xy123",
                "items": 2
            }
        }
      ]
    }
}