--- title: "json_valid" slug: "dev-database-reference-sql-functions-json-json-valid" updated: 2026-10-30T18:19:06Z published: 2026-10-30T18:19:06Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-json-json-valid" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # json_valid *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Parsing and JSON functions](/develop/docs/dev-database-reference-sql-functions-json) › 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) ``` | Argument | Type | Description | | --- | --- | --- | | `text` | `VARCHAR` or `JSON` | The text to check. | **Returns:** `BOOLEAN`. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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, "") 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. | Valid (`true`) | Not valid (`false`) | | --- | --- | | `'{"a": 1}'`, `'[1, 2]'`, `'{}'`, `'[]'` | `'not json'`, `''` (empty), `' '` (a space) | | `'"s"'`, `'42'`, `'true'`, `'null'` | `'True'`, `'NULL'` (JSON keywords are lower case) | | `' {"a" : 1 } '`, `'{"a": 1, "a": 2}'` | `'{''a'': 1}'` (single quotes), `'{"a": 01}'` (a leading zero) | | `'{"a": 1,}'` (a trailing comma) | `'{"a": 1} x'`, `'[1, 2]x'` (text after the document) | | `'{"a": NaN}'`, `'Infinity'` | `'{'`, `'[1, 2'`, `'"unterminated'` (cut short) | | `'{"a": 12345678901234567890}'` (a big number) | `'{"a": "\x"}'` (an invalid escape), `'{"a": undefined}'` | ``` 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 | Input | Result | | --- | --- | | `NULL` | `NULL` | | A newline after the document, `'{"a": 1}'` and `\n` | `true` | | A byte-order mark (U+FEFF) before the document | `false` | | An unpaired surrogate escape, `'{"a": "\ud800"}'` | `false` | | A raw line break inside a string | `false` | | 1,000 nested arrays | `true` | | A `BLOB` | `false` | | A number | Error: `No function matches the given name and argument types 'json_valid(INTEGER_LITERAL)'` | | Text cast to `JSON` that isn't valid, `'not json'::JSON` | Error: `Malformed JSON at byte 0 of input: invalid literal. Input: not json` (from the cast) | 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 | Function | `'{"a": 1}'` | `'not json'` | | --- | --- | --- | | `json_valid` | `true` | `false` | | [json_type](/develop/docs/dev-database-reference-sql-functions-json-json-type) | `'OBJECT'` | Error: `Malformed JSON ...` | | [json_extract](/develop/docs/dev-database-reference-sql-functions-json-json-extract) with `'$'` | `'{"a":1}'` | Error: `Malformed JSON ...` | ``` 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](/develop/docs/dev-database-reference-sql-functions-json): all the JSON functions, and [invalid JSON in a column](/develop/docs/dev-database-reference-sql-functions-json). - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.