Examples | Create JSON Output | SCRIPT Table Operator | Vantage - Example: Creating JSON Output by Using the SCRIPT Table Operator to Invoke Python Modules - Advanced SQL Engine - Teradata Database

The following example uses some of Python built-in modules to create a JSON object from a website URL query string. This is useful in converting web URL data to JSON so it can be queried using the JSON data type.

The urltojson.py Python script:

import sys
import json
from urllib.parse import urlparse
from urllib.parse import parse_qs
for line in sys.stdin:
    print(json.dumps(parse_qs(urlparse(
                     line.rstrip('\n')).query)))

SQL to install and run the script on sample data:

DATABASE mytestdb;
CREATE TABLE sourcetab (url VARCHAR(10000));
INS sourcetab('https://www.google.com/finance?q=NYSE:TDC');
INS sourcetab('http://www.ebay.com/sch/i.html?_trksid=p2050601.m570.l1313.TR0.TRC0.H0.Xteradata+merchandise&_nkw=teradata+merchandise&_sacat=0&_from=R40');
INS sourcetab('https://www.youtube.com/results?search_query=teradata%20commercial&sm=3');
INS sourcetab('https://www.contrivedexample.com/example?mylist=1&mylist=2&mylist=...testing');
-- This assumes that urltojson.py is in the current directory.
CALL SYSUIF.INSTALL_FILE('urltojson', 'urltojson.py', 'cz!urltojson.py');
SET SESSION SEARCHUIFDBPATH = mytestdb;
SELECT CAST(JSONresult as JSON)
FROM SCRIPT (
    ON (SELECT url FROM sourcetab)
    SCRIPT_COMMAND('/opt/teradata/languages/Python/bin/python3 ./mytestdb/urltojson.py')
    RETURNS('JSONresult VARCHAR(10000)')
);

The result:

JSONresult
----------------------------------------------------------------------------------------
{"q": ["NYSE:TDC"]}
{"mylist": ["1", "2", "...testing"]}
{"_from": ["R40"], "_trksid": ["p2050601.m570.l1313.TR0.TRC0.H0.Xteradata merchandise"], "_nkw": ["teradata merchandise"], "_sacat": ["0"]}
{"search_query": ["teradata commercial"], "sm": ["3"]}