JSON Data | Query Foreign Table | Teradata Vantage - Example: Querying a Foreign Table Containing JSON Data - Advanced SQL Engine - Teradata Database

Table Definition

Below is the table definition for this example. This statement defines the foreign table riverflow where the LOCATION parameter specifies a JSON file stored on Amazon S3.

Create the DefAuth authorization object, if not already done. See Example: Creating an Authorization Object.

CREATE FOREIGN TABLE riverflow
, EXTERNAL SECURITY DEFINER TRUSTED DefAuth
USING
(
     LOCATION ('/s3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/06/27')
);

Display Keys in JSON File

This query uses the JSON_KEYS function to display the keys in the JSON file specified by the foreign table riverflow.

SELECT DISTINCT * FROM JSON_KEYS (ON (SELECT payload FROM riverflow)) AS j;

The results display as follows.

 JSONKeys        
 --------------- 
 "Flow"         
 "Precipitation"
 "Conductance"  
 "datetime"     
 "site_no"      
 "Temp"         
 "GageHeight"

Sample Query and Results

This query returns the rows from the foreign table riverflow where the temperature is over 11.0 and the gage height is more than 10.00, ordered by date and time.

SELECT 
  payload.Flow, 
  payload.Precipitation, 
  payload.Conductance, 
  payload.datetime, 
  payload.site_no, 
  payload.Temp, 
  payload.GageHeight
FROM riverflow 
WHERE payload.Temp>11.0 
  AND payload.GageHeight>10.00 
ORDER BY payload.datetime;

Here is a portion of the results:

 Payload.Flow Payload.Precipitation Payload.Conductance Payload.datetime Payload.site_no Payload.Temp Payload.GageHeight 
 ------------ --------------------- ------------------- ---------------- --------------- ------------ ------------------ 
 16400        0.00                  668                 2018-06-27 14:15 09380000        11.8         10.01             
 16400        0.00                  670                 2018-06-27 14:30 09380000        11.8         10.01             
 16600        0.00                  665                 2018-06-27 14:45 09380000        11.9         10.04             
 16600        0.00                  669                 2018-06-27 15:00 09380000        11.9         10.05             
 16700        0.00                  668                 2018-06-27 15:15 09380000        12.0         10.07             
 16700        0.00                  667                 2018-06-27 15:30 09380000        12.0         10.07             
 16700        0.00                  663                 2018-06-27 15:45 09380000        12.0         10.07             
 16800        0.00                  666                 2018-06-27 16:00 09380000        12.0         10.09             
 16800        0.00                  667                 2018-06-27 16:15 09380000        12.0         10.08             
 16800        0.00                  667                 2018-06-27 16:30 09380000        12.0         10.08             
 16800        0.00                  668                 2018-06-27 16:45 09380000        12.0         10.09