json_valid

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Check and inspect. Returns true if text is a valid JSON document and false if it isn't. Unlike the functions that read JSON, it doesn't fail on invalid input, so you can use it to find or count the documents that would stop a query.

Syntax

json_valid(text)

Returns: BOOLEAN.

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_valid. 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 six events with a payload column. Rows 3 and 4 aren't valid JSON, row 5 has a trailing comma, and the last row has no value:

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_valid"'

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}.events"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, payload VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '{\"user\": \"ada\", \"n\": 3}'), "
    "(2, '[10, 20]'), "
    "(3, 'not json'), "
    "(4, ''), "
    "(5, '{\"user\": \"alan\",}'), "
    "(6, NULL)")

[]
[]
[(6,)]

What it does

json_valid parses the text and returns whether it's one complete JSON document. A document can be an object, an array, or a single string, number, boolean or null. Spaces around the document, and a newline after it, are allowed. The parser also accepts a few things that strict JSON doesn't: a trailing comma, and NaN and Infinity as numbers. The functions that read JSON accept a trailing comma and NaN too.

run("SELECT json_valid('{\"a\": 1}'), json_valid('[1, 2]'), json_valid('{}'), json_valid('[]')")
run("SELECT json_valid('\"s\"'), json_valid('42'), json_valid('true'), json_valid('null')")
run("SELECT json_valid('  {\"a\" : 1 }  '), json_valid('{\"a\": 1, \"a\": 2}'), json_valid('{\"a\": 1,}'), "
    "json_valid('{\"a\": NaN}'), json_valid('Infinity'), json_valid('{\"a\": 12345678901234567890}')")
run("SELECT json_valid('not json'), json_valid(''), json_valid(' '), json_valid('True'), json_valid('NULL')")
run("SELECT json_valid('{''a'': 1}'), json_valid('{\"a\": 01}'), json_valid('{\"a\": 1} x'), json_valid('[1, 2]x')")
run("SELECT json_valid('{'), json_valid('[1, 2'), json_valid('\"unterminated'), json_valid('{\"a\": \"\\x\"}'), "
    "json_valid('{\"a\": undefined}')")

[(True, True, True, True)]
[(True, True, True, True)]
[(True, True, True, True, True, True)]
[(False, False, False, False, False)]
[(False, False, False, False)]
[(False, False, False, False, False)]

NULLs and edge cases

The same cases, as the cluster returns them:

run("SELECT json_valid(NULL), json_valid('{\"a\": 1}\n'), json_valid('\ufeff{}'), "
    "json_valid('{\"a\": \"\\ud800\"}'), json_valid('{\"a\": \"line\nbreak\"}')")
run("SELECT json_valid(repeat('[', 1000) || repeat(']', 1000)), json_valid('\\xAA'::BLOB)")
run("SELECT json_valid(123)")
run("SELECT json_valid('not json'::JSON)")

[(None, True, False, False, False)]
[(True, False)]
InternalError: Binder Error: No function matches the given name and argument types 'json_valid(INTEGER_LITERAL)'. You might need to add explicit type casts.
OSError: Malformed JSON at byte 0 of input: invalid literal.  Input: not json

Examples

Check each document. Row 5, with its trailing comma, is valid:

run(f"SELECT id, json_valid(payload) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, True), (2, True), (3, False), (4, False), (5, True), (6, None)]

Count the valid documents. count(*) counts every row, including the NULL:

run(f"SELECT count(*) FILTER (WHERE json_valid(payload)), count(*) FROM {TABLE}")

[(3, 6)]

List the rows that would stop a query that reads the JSON:

run(f"SELECT id, payload FROM {TABLE} WHERE NOT json_valid(payload) ORDER BY id LIMIT 10")

[(3, 'not json'), (4, '')]

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). VAST DataBase stores JSON as text and doesn't check it:

• Nothing checks JSON when you write it. A column declared JSON is created as VARCHAR, and an INSERT of 'not json' into it succeeds. An explicit cast, '...'::JSON, does parse the text, and fails on invalid JSON.
• json_valid is computed by the Query Engine. In the SELECT list, the scan reads the whole column and the Query Engine parses each value. In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine keeps the rows that match.

The plan of a WHERE condition shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE json_valid(payload)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│    json_valid(payload)    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          payload          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Validate when you write. Check documents with json_valid (or in your application) before you insert them, so no query has to find a bad row later.
• Store the result if you filter on it often. A BOOLEAN column such as payload_ok, computed when you write the row, is evaluated in VAST DataBase; json_valid(payload) parses every row in the Query Engine.

Limitations and errors

• Text only. A number fails with No function matches the given name and argument types; the candidates are json_valid(VARCHAR) and json_valid(JSON). A BLOB returns false. A DATE, BOOLEAN or list returns true; pass text, not other types.
• A cast to JSON fails before json_valid runs. json_valid(x::JSON) stops the query on invalid text instead of returning false. Pass the VARCHAR value.
• The parser is lenient. A trailing comma, NaN and Infinity are valid here, though strict JSON parsers reject them. Check in your application if other systems read the same documents.

Similar functions

run("SELECT json_valid('{\"a\": 1}'), json_type('{\"a\": 1}'), json_extract('{\"a\": 1}', '$')")
run("SELECT json_valid('not json')")
run("SELECT json_type('not json')")
run("SELECT json_extract('not json', '$')")

[(True, 'OBJECT', '{"a":1}')]
[(False,)]
OSError: Malformed JSON at byte 0 of input: invalid literal.  Input: not json
OSError: Malformed JSON at byte 0 of input: invalid literal.  Input: not json

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, and invalid JSON in a column.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.