JSON functions

JSON_EXTRACT

JSON_EXTRACT_ARRAY

JSON_VALUE

JSON_VALUE_ARRAY

JSON pointer expression syntax

The placeholder <json_pointer_expression> indicates where you should use a JSON pointer, which is a way to access specific elements in a JSON document. For a formal specification, see RFC6901.

A JSON pointer starts with a forward slash (/), which denotes the root of the JSON document. This is followed by a sequence of property (key) names or zero-based ordinal numbers separated by slashes. You can specify property names or use ordinal numbers to specify the Nth property or the Nth element of an array.

The tilde (~) and forward slash (/) characters have special meanings and need to be escaped according to the guidelines below:

  • To specify a literal tilde (~), use ~0
  • To specify a literal slash (/), use ~1

For example, consider the JSON document below.

{
    "key": 123,
    "key~with~tilde": 2,
    "key/with/slash": 3,
    "value": {
      "dyid": 987,
      "keywords" : ["insanely","fast","analytics"]
    }
}

With this JSON document, the JSON pointer expressions below evaluate to the results shown.

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 they keywords property.

JSON common example

Usage examples for JSON functions in this reference are based on the JSON document below, which is indicated using the <json_common_example> placeholder.

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