Setting Up the Examples Using Dot Notation in SELECT and WHERE Clause

Teradata Vantageā„¢ JSON Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K

The JSON object, table, and data in this section are used in the examples in the following sections to show the usage of dot notation in a SELECT list and in a WHERE clause.

See JSONPath Request Syntax for information about the following JSONPath syntax elements used in the examples:
  • Recursive descent operator
  • Wildcard operator
  • Named list operator
  • Index list operator
  • Slice operator
The examples reference the following JSON object:
{
   "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 }  
      ]
}
The examples reference the following table and data:
CREATE TABLE jsonEnhancedDotNotationTbl(
   id INT,
   jsonCol01 JSON(1000) CHARACTER SET LATIN, 
   jsonCol02 JSON(1000) CHARACTER SET UNICODE,
   jsonCol03 JSON(1000) STORAGE FORMAT BSON,
   jsonCol04 JSON(1000) STORAGE FORMAT UBJSON);
INSERT INTO jsonEnhancedDotNotationTbl(1,
'{
   "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 }  
      ]
}',
'{
   "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 }  
      ]
}',
'{
   "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 }  
      ]
}',
'{
   "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 }  
      ]
}');