json_serialize_sql

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › SQL as JSON. Parses the text of one or more SELECT statements and returns their syntax tree as a JSON document. Use it to inspect queries in a program, for example to find the tables a saved query reads. json_deserialize_sql turns the tree back into SQL text.

Syntax

json_serialize_sql(sql)
json_serialize_sql(sql, skip_null := true, skip_empty := true, format := true)

The options are named: pass them as name := value, in any combination.

Returns: JSON. The driver returns it as text (a Python str).

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_json_json_serialize_sql. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of saved queries: two SELECT statements, two statements in one text, an INSERT, and a NULL:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_json_json_serialize_sql"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.saved_queries"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, q VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'SELECT name FROM people WHERE id = 1'), "
    "(2, 'select a + 1 from t where b = ''x'' order by 1 limit 2'), "
    "(3, 'SELECT 1; SELECT 2'), "
    "(4, 'INSERT INTO x VALUES (1)'), "
    "(5, NULL)")

[]
[]
[(5,)]

What it does

json_serialize_sql parses the text without running it, and returns an object:

• For SELECT statements, "error": false and a statements array with one syntax tree per statement. Each tree is under node, and its type is SELECT_NODE. A WITH query is a SELECT statement too.
• For anything else, such as an INSERT or text that isn't SQL, "error": true, with error_type (for example parser) and error_message. The query doesn't fail.

The tree of the shortest query, with the null and empty members left out:

run("SELECT json_serialize_sql('SELECT 1', skip_null := true, skip_empty := true)")

[('{"error":false,"statements":[{"node":{"type":"SELECT_NODE","select_list":[{"class":"CONSTANT","type":"VALUE_CONSTANT","query_location":7,"value":{"type":{"id":"INTEGER"},"is_null":false,"value":1}}],"from_table":{"type":"EMPTY","query_location":18446744073709551615},"aggregate_handling":"STANDARD_HANDLING"}}]}',)]

Read single fields from the tree by path, with ->> or json_extract_string:

run("SELECT json_serialize_sql('SELECT 1 AS x') ->> '$.error', "
    "json_serialize_sql('SELECT 1 AS x') ->> '$.statements[0].node.type'")
run("SELECT json_array_length(json_serialize_sql('SELECT 1; SELECT 2'), '$.statements')")

[('false', 'SELECT_NODE')]
[(2,)]

NULLs and edge cases

run("SELECT json_serialize_sql(NULL), json_serialize_sql(NULL, skip_null := true)")
run("SELECT json_serialize_sql('') ->> '$.error', "
    "json_serialize_sql('WITH a AS (SELECT 1 AS x) SELECT x FROM a') ->> '$.error', "
    "json_serialize_sql('INSERT INTO x VALUES (1)') ->> '$.error'")
run("SELECT json_serialize_sql('not sql') ->> '$.error', json_serialize_sql('not sql') ->> '$.error_type', "
    "json_serialize_sql('not sql') ->> '$.error_message'")
run("SELECT length(json_serialize_sql('SELECT 1')) > length(json_serialize_sql('SELECT 1', skip_null := true)), "
    "length(json_serialize_sql('SELECT 1', format := true)) > length(json_serialize_sql('SELECT 1'))")

[(None, None)]
[('false', 'false', 'true')]
[('true', 'parser', 'syntax error at or near "not"')]
[(True, True)]

Examples

Check each saved query, and count its statements. The INSERT gives true for the error, and the NULL row gives NULL:

run(f"SELECT id, json_serialize_sql(q) ->> '$.error', json_array_length(json_serialize_sql(q), '$.statements') "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'false', 1), (2, 'false', 1), (3, 'false', 2), (4, 'true', None), (5, None, None)]

Find the table that each single-statement query reads:

run(f"SELECT id, json_serialize_sql(q) ->> '$.statements[0].node.from_table.table_name' "
    f"FROM {TABLE} WHERE id < 3 ORDER BY id LIMIT 10")

[(1, 'people'), (2, 't')]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). json_serialize_sql parses the text of each row in the Query Engine; the statements aren't run. In the SELECT list, the scan reads the column and a projection step parses it for each row it returns:

plan(f"SELECT json_serialize_sql(q) FROM {TABLE}")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│   json_serialize_sql(q)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│       Projections: q      │
│                           │
└───────────────────────────┘

Make it faster

• Read only the fields you need. A whole tree is large even for a short query; extract the fields by path, as in the examples, rather than returning the document.
• Select the rows with a condition on a column. A comparison such as id < 3 is evaluated in VAST DataBase, so only those rows are parsed.
• Use skip_null and skip_empty when you do return the tree: they leave out the members that carry no information.

Limitations and errors

• SELECT statements only. Other statements give "error": true instead of a tree.
• Don't return the whole error object. Besides error, error_type and error_message, it holds long diagnostic fields, such as a server stack trace. Read the three fields by path.
• The options are named. A positional option, such as json_serialize_sql('SELECT 1', 1), fails with No function matches the given name and argument types 'json_serialize_sql(STRING_LITERAL, INTEGER_LITERAL)'. A misspelt name fails with json_serialize_sql: Unknown argument 'skip_nulls'.
• Text only. A number or a boolean fails with No function matches the given name and argument types.
• A tree written with skip_null and skip_empty can't be turned back into SQL by json_deserialize_sql. Serialize without them if you need the SQL again.

run("SELECT json_serialize_sql('SELECT 1', 1)")
run("SELECT json_serialize_sql('SELECT 1', skip_nulls := true)")
run("SELECT json_serialize_sql(123)")

InternalError: Binder Error: No function matches the given name and argument types 'json_serialize_sql(STRING_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: json_serialize_sql: Unknown argument 'skip_nulls'
InternalError: Binder Error: No function matches the given name and argument types 'json_serialize_sql(INTEGER_LITERAL)'. You might need to add explicit type casts.

Similar functions

run("SELECT json_deserialize_sql(json_serialize_sql('SELECT 1 AS x'))")

[('SELECT 1 AS x',)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Parsing and JSON functions: all the JSON functions.
• json_deserialize_sql and json_extract_string.
• EXPLAIN: how to read a plan.