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.

These operators extract a field from a JSON value, returing another JSON value:
- Dot Operator (.)
- Subscript Operator ([])
These functions convert a JSON value to a SQL value:
- JSON_VALUE
These functions convert a SQL value to a JSON value:
- TO_JSON

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:

These functions extract part of the document, but preserve the original data in the result:
These functions convert a JSON value to a SQL value:
This function extracts a JSON value and converts it to a SQL value, combining the functionalities of the previous two types of functions:
- JSON_POINTER_EXTRACT_TEXT

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:

Pointer	Result	Notes
`/`	`{` `"key": 123,` `"key~with~tilde": 2,` `"key/with/slash": 3,` `"value": {` `"dyid": 987,` `"keywords" : ["insanely","fast","analytics"]` `}`	Returns the whole document.
`/key`	123
`/key~0with~0tilde`	2	Indicates the value associated with the `key~with~tilde` property name.
`/key~1with~1slash`	3	Indicates the value associated with the `key/with/slash` property name.
`/0`	123	Uses an ordinal to indicate the value associated with the `key` property name. The `key` property is in the first 0-based position.
`/value/keywords/2`	analytics	Indicates 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
            }
        }
      ]
    }
}

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

JSON functions

JSON Functions Operating on JSON Data

JSON Functions Operating on TEXT Data

JSON pointer expression syntax

JSON common example

​JSON Functions Operating on JSON Data

​JSON Functions Operating on TEXT Data

​JSON pointer expression syntax

​JSON common example

JSON Functions Operating on JSON Data

JSON Functions Operating on TEXT Data

JSON pointer expression syntax

JSON common example