Setting Up the Examples Using Dot Notation in SELECT and WHERE Clause - Advanced SQL Engine - Teradata Database

See JSONPath Request 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 }  
      ]
}');