15.10 - JSONPath Request Syntax - Teradata Database

Teradata Database Teradata JSON
Product
Teradata Database
Release Number
15.10
Published
December 2015
Language
English (United States)
Last Update
2018-06-05
dita:mapPath
TeradataJSON_15_10.ditamap
dita:ditavalPath
ft:empty

You can use JSONPath to request information about a particular portion of a JSON instance. The entity desired can be any portion of a JSON instance, such as a name/value pair, an object, an array, an array element, or a value. Also, several JSON functions and methods accept a JSONPath formatted string as input and operate on the specified entity.

The principles of JSONPath are analogous to those of XPath for XML.

Teradata does not provide complete validation of the syntax so malformed query strings can produce undesirable results. Teradata only provides the following validations:

  • Validate the presence of the '$' root character.
  • Validate the omission of the following characters:
    • Any ';' characters
    • Three or more consecutive '.' characters. For example: '…'

Syntax

The following shows the JSONPath syntax as specified in the JSONPath Specification located at http://goessner.net/articles/JsonPath/.















Syntax Elements

$
The root object or element.
children
The descent operator ('.' or '.. ') followed by a child specification or options.
child specification
  • The wildcard character ('*') which matches all objects or elements.
  • A string specifying the name of a particular object or element and associated options if needed.
options
An index, an expression, or a filter.
integer
A signed integer.
expression
In this context, LENGTH is the length of the current JSON array, equal to the number of elements in the array.
@
The current object or element.
filter
Applies a filter (script) expression.
element_string
A string specifying the name of an element.
=~ string
String comparison expression.

Example: Elements of the JSONPath Syntax

This example uses the following JSON instance to illustrate particular elements of the JSONPath syntax. The corresponding table provides explanations of the syntax elements and usage examples.

{
    "customer" : "CustomerName",
    "orderID" : 3,
    "price" : "$100000.00",
    "items" :
       [
          { "ID" : 1, "name" : "disk", "amt" : 10 },
          { "ID" : 2, "name" : "RAM", "amt" : 20 },
          { "ID" : 3, "name" : "monitor", "amt" : 30 },
          { "ID" : 4, "name" : "keyboard", "amt" : 40 },
          { "ID" : 5, "name" : "camera", "amt" : 50 },
          { "ID" : 6, "name" : "button", "amt" : 60 },
          { "ID" : 7, "name" : "mouse", "amt" : 70 },
          { "ID" : 8, "name" : "pen", "amt" : 80 }
       ]
}
JSONPath Description Example Explanation of Example Result
$ The root object/element $.customer The name of the customer CustomerName
@ The current object/element $.items[(@.length-1)] The last item in the order.

The use of the 'length' keyword in this context is interpreted as the length of the current JSON array and is treated as a property of the JSON array. This is only interpreted in this manner if 'length' occurs immediately after the '@.' syntax. If the word 'length' is found later in the expression (for example, '@.firstChild.length'), it is interpreted as the name of a child of some entity, not as a property of that entity.

 {"ID":8,"name":"pen","amt":80}
.. Recursive descent $..name All item names ["disk","RAM","monitor", "keyboard","camera","button","mouse","pen"]
* Wildcard

All objects/elements regardless of their names

 $.items[0].* All descriptions of the first item of the order [1,"disk",10]
[ ] The native array operator $.items[0] The first item {"ID":1,"name":"disk","amt":10}
[start,end] List of indexes $.items[0,1] The first two items [{"ID":1,"name":"disk","amt":10},

{"ID":2,"name":"RAM","amt":20}]
[start:end:step] Array slice operator

If you do not specify start, the default is the first index.

If you do not specify end, the default is the last index.

If you do not specify step, the default is a step of 1.

 $.items[0:4:2] All items from 1-5 (not inclusive on the end index) by a step of 2 (That is, items 1 and 3) [{"ID":1,"name":"disk","amt":10},

{"ID":3,"name":"monitor","amt":30}]
?( ) Applies a filter (script) expression $.items[?(@.amt<50)] Filter all items of which a quantity less than 50 was ordered [{"ID":1,"name":"disk","amt":10},

{"ID":2,"name":"RAM","amt":20},

{"ID":3,"name":"monitor","amt":30},

{"ID":4,"name":"keyboard","amt":40}]
( ) Script expression, using the underlying script engine $.items[(@.length-1)] The last item in the order {"ID":8,"name":"pen","amt":80}