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