JSON Data Keys | JSON_KEYS Function | Teradata Vantage - 17.05 - Example: Displaying Keys in a Foreign Table Containing JSON Data - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
This example shows how to use the JSON_KEYS function to display the keys in a foreign table with JSON data.
For a foreign table, the syntax of the query does not change based on whether there is an extension or not. This is in contrast to using READ_NOS to extract the information directly, without using a foreign table intermediary.

Table Definition

Below is the table definition for this example. This statement defines the foreign table riverflow where the LOCATION parameter specifies a 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 Data

This SELECT statement uses the JSON_KEYS function to display the keys in the foreign table riverflow, which contains JSON data.

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

The results display as follows.

JSONKeys
"BatteryVoltage"
"Flow"
"Precipitation"
"Conductance"
"Velocity"
"datetime"
"WaterVelocity"
"GageHeight2"
"site_no"
"Temp"
"GageHeight"

Sample Query and Results

This query searches for entries where the temperature was greater than 11 and the height was 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 
AS derived_table WHERE payload.Temp>11.0 AND payload.GageHeight>10.00 ORDER BY payload.datetime;

Below is a sample of the output.

 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             
 16900        0.00                  670                 2018-06-27 17:00 09380000        12.0         10.10