This example shows how to use the READ_NOS table operator to access an external JSON file that includes the extension .JSON.
File Name Query
If not already done, create the authorization object. See Example: Create an Authorization Object.
This query uses READ_NOS to query the file at the specified location with a return type of NOSREAD_KEYS. The LOCATION parameter specifies a JSON file stored on Amazon S3.
Perform the READ_NOS:
SELECT location FROM ( LOCATION='/S3/td-usgs-public.s3.amazonaws.com/JSONDATA/09380000/2018/06/27' AUTHORIZATION=MyAuthObj RETURNTYPE='NOSREAD_KEYS' ) AS derived_table;
The result shows that the file includes the extension, .JSON.
Location -------------------------------------------------------------- /S3/s3.amazonaws.com/td-usgs-public/JSONDATA/09380000/2018/06/27.json
Sample Query and Results
This query uses READ_NOS to return specific columns searching for entries where the temperature was greater than 11 and the height was more than 10.00, ordered by date and time.
If not already done, create the authorization object. See Example: Create an Authorization Object.
Perform the READ_NOS:
SELECT payload.Flow, payload.Precipitation, payload.Conductance, payload.datetime, payload.site_no, payload.Temp, payload.GageHeight FROM ( LOCATION='/S3/td-usgs-public.s3.amazonaws.com/JSONDATA/09380000/2018/06/27' AUTHORIZATION=MyAuthObj ) AS derived_table WHERE payload.Temp > 11.0 AND payload.GageHeight > 10.00 ORDER BY payload.datetime;
Your result is similar to the following:
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 [...]