This example uses the READ_NOS table operator with JSON_KEYS function to display the keys in a JSON file.
Display Keys in a JSON File
This SELECT statement uses the READ_NOS_json_intable_fm function mapping, which includes the ANY IN TABLE clause, with the JSON_KEYS function to display the keys in the external JSON file 27.
Create the function mapping READ_NOS_json_intable_fm, if not already created. See Example: Function Mapping Definition Using ANY IN TABLE for JSON Data Files.
Perform the READ_NOS:
SELECT DISTINCT * FROM JSON_KEYS (
ON (
SELECT payload FROM READ_NOS_json_intable_fm (
ON (SELECT CAST(NULL AS JSON) )
USING
LOCATION('/s3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/06/27')
RETURNTYPE('NOSREAD_RECORD')
) AS read_nos_query
) AS json_on_keys_query ) AS d ;
Result:
JSONKeys --------------- "Flow" "Precipitation" "Conductance" "datetime" "site_no" "Temp" "GageHeight"