json_deserialize_sql

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › SQL as JSON. Turns the JSON syntax tree of a SELECT statement, as json_serialize_sql returns it, back into SQL text. A round trip through both functions writes a query in a normalised form.

Syntax

json_deserialize_sql(tree)

Returns: VARCHAR.

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_deserialize_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: three SELECT statements written in different styles, 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_deserialize_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, 'WITH a AS (SELECT 1 AS x) SELECT x FROM a'), "
    "(4, NULL)")

[]
[]
[(4,)]

What it does

json_deserialize_sql writes the statement in the tree as SQL text. The text is rebuilt from the tree, not copied, so it's normalised:

• Keywords are in capitals: select becomes SELECT.
• Expressions are in parentheses: a+1 becomes (a + 1), and b = 'x' becomes (b = 'x').
• A column name such as name is written in double quotes: SELECT "name" FROM people.
• A WITH clause is written without a space before the main SELECT: WITH a AS (SELECT 1 AS x)SELECT x FROM a.

run("SELECT json_deserialize_sql(json_serialize_sql('SELECT 1 AS x'))")
run("SELECT json_deserialize_sql(json_serialize_sql('select a+1 from t where b = ''x'' order by 1 limit 2'))")

[('SELECT 1 AS x',)]
[("SELECT (a + 1) FROM t WHERE (b = 'x') ORDER BY 1 LIMIT 2",)]

NULLs and edge cases

run("SELECT json_deserialize_sql(NULL), json_deserialize_sql(json_serialize_sql(NULL))")
run("SELECT json_deserialize_sql(json_serialize_sql('SELECT 1; SELECT 2'))")
run("SELECT json_deserialize_sql(json_serialize_sql('not sql'))")
run("SELECT json_deserialize_sql(json_serialize_sql('SELECT 1', skip_null := true, skip_empty := true))")
run("SELECT json_deserialize_sql('{}')")
run("SELECT json_deserialize_sql('{\"statements\": []}')")
run("SELECT json_deserialize_sql('not json')")

[(None, None)]
OSError: Error parsing json: more than one statement
OSError: Error parsing json: parser: syntax error at or near "not"
OSError: Expected but did not find property 'cte_map' in json object: '{"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"}'
OSError: Error parsing json: no statements array
OSError: Error parsing json: no statements
OSError: Malformed JSON at byte 0 of input: invalid literal.  Input: not json

Examples

Each saved query in normalised form. The NULL row stays NULL:

run(f"SELECT id, json_deserialize_sql(json_serialize_sql(q)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'SELECT "name" FROM people WHERE (id = 1)'), (2, "SELECT (a + 1) FROM t WHERE (b = 'x') ORDER BY 1 LIMIT 2"), (3, 'WITH a AS (SELECT 1 AS x)SELECT x FROM a'), (4, None)]

A row whose text isn't a single SELECT statement fails the whole query. Add one, then keep only the rows that json_serialize_sql parsed without an error:

run(f"INSERT INTO {TABLE} VALUES (5, 'not sql')")
run(f"SELECT id, json_deserialize_sql(json_serialize_sql(q)) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, json_deserialize_sql(json_serialize_sql(q)) FROM {TABLE} "
    f"WHERE json_serialize_sql(q) ->> '$.error' = 'false' ORDER BY id LIMIT 10")

[(1,)]
OSError: Error parsing json: parser: syntax error at or near "not"
[(1, 'SELECT "name" FROM people WHERE (id = 1)'), (2, "SELECT (a + 1) FROM t WHERE (b = 'x') ORDER BY 1 LIMIT 2"), (3, 'WITH a AS (SELECT 1 AS x)SELECT x FROM a')]

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_deserialize_sql reads the tree and writes the text in the Query Engine; nothing is run. In the SELECT list, the scan reads the column and a projection step computes the text for each row it returns:

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

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

Make it faster

• Normalise when you write. Store the normalised text in its own column when you save a query. Then a comparison of that column with a constant is evaluated in VAST DataBase, without parsing any text.
• Select the rows with a condition on a column, such as id = 1, which VAST DataBase evaluates, so only those rows are parsed.

Limitations and errors

• One SELECT statement per tree. A tree of several statements fails with Error parsing json: more than one statement; split the text into statements first.
• Full trees only. A tree written with skip_null and skip_empty fails with Expected but did not find property .... Serialize without those options.
• An error object fails the query. If json_serialize_sql couldn't parse the text, json_deserialize_sql fails with Error parsing json: and the parser's message. Check json_serialize_sql(q) ->> '$.error' first, as in the second example.
• The original layout isn't kept, because the text is rebuilt from the tree.

Similar functions

run("SELECT json_serialize_sql('SELECT 1 AS x') ->> '$.error', "
    "json_deserialize_sql(json_serialize_sql('SELECT 1 AS x'))")

[('false', '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_serialize_sql.
• EXPLAIN: how to read a plan.