As we know, Python has a built-in package called JSON. There are few JSON methods which we use in our day-to-day task to process the JSON files. During this post we will be discussing about commonly used four methods e.g. (json.load(),json.loads(),json.dump(),json.dumps()). Though there are multiple blogs on internet which describes these methods in detail but as part of this post we will continue our discussion w.r.t the Snowflake specially loads() and dumps() methods.
We will consider the use case where JSON methods loads() and dumps() being used inside the AWS lambda function. Consider the scenario where Snowflake External function calls the AWS lambda function via the AWS API gateway, Lambda function retrieve the data from the URL and after the processing, Lambda return this output in JSON formatted string back to Snowflake.
Example:
For example, Receive an HTTP request and store it in a Python dictionary or any Python object using json.loads().Send that data back to the requested application (Snowflake) so you need to convert the Python dictionary object into a JSON formatted string to send as a response in JSON string. To do this you need to use json.dumps().
json.load() : it accepts a file object. To read JSON data from a file and convert it into a dictionary.
with open("data_file.json", "r") as read_content:
print(json.load(read_content))
Json.loads(): to convert JSON string to a dictionary. Sometimes we receive JSON response in string format. So to use it in our application, we need to convert JSON string into a Python dictionary.
Json.dump(): Used for writing the Python object i.e. dict to JSON file.
For example, you have data in a list or dictionary or any Python object, and you want to encode and store it in a file in the form of JSON.
Json.dumps(): method can convert a Python object into a JSON string.
Lambda Use case:
AWS Lambda Function:
Lambda
Above lambda has been tested with following Test Event:
{
“body”: “{\”data\”:[[\”stations\”, \”https://gbfs.citibikenyc.com/gbfs/en/station_information.json\”],[\”regions\”, \”https://gbfs.citibikenyc.com/gbfs/en/system_regions.json\”]]}”
}
Test Execution Result:
Now try to understand with the Test Execution results:
Payload : As we have observer the input to the lambda (capture by body_event) is JSON sting and it has been converted to the PYTHON object and stored into the Payload.
sflkRowRef: Once the data has been converted into the DICT, fetch the values related to the keys. There are two key in Dictionary and sflkRowRef variable stores these Keys (Stations,regions)
URL : Extract the value w.r.t the Key i.e. Stations.
json_obj = json.loads(httpData): Receive an HTTP request and store it in a Python dictionary or any Python object using json.loads(). Below is the output pf json_obj after converting to Dictionary.
json_compatible_string_to_return = json.dumps({“data” : retVal[“data”]}):
After finalizing the response converts the python dict to the JSON formatted string which will be passed to the Snowflake.