json_value

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Read values. Returns the scalar value (a string, number, or boolean) at a path in a JSON document, as JSON text: a string keeps its quotes. For an object, an array or JSON null, it returns NULL.

Syntax

json_value(json, path)
json_value(json, paths)

Returns: VARCHAR, holding JSON text. A list of paths, or a path with a wildcard, 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_value. 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 four events with a JSON payload column: two objects, an array and a NULL. DOC is a sample document for the examples that don't read the table:

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

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, \"tags\": [\"a\", \"b\"], \"geo\": {\"city\": \"Oslo\", \"lat\": 59.9}}'), "
    "(2, '{\"user\": \"alan\", \"n\": \"many\", \"tags\": [], \"geo\": null}'), "
    "(3, '[10, 20, [30, 40]]'), "
    "(4, NULL)")

DOC = """'{"user": "ada", "n": 3, "tags": ["a", "b", "c"], "geo": {"city": "Oslo", "lat": 59.9}, "z": null}'"""

[]
[]
[(4,)]

What it does

json_value reads a value with the same path forms as json_extract, and returns it only if it's a scalar:

With a wildcard or a list of paths, the result is a list with one element per match, and NULL for each match that isn't a scalar.

run(f"SELECT json_value({DOC}, '$.user'), json_value({DOC}, '$.n'), json_value({DOC}, '$.geo'), "
    f"json_value({DOC}, '$.tags'), json_value({DOC}, '$.z')")
run(f"SELECT json_value({DOC}, '$.*')")

[('"ada"', '3', None, None, None)]
[(['"ada"', '3', None, None, None],)]

NULLs and edge cases

The same cases, as the cluster returns them:

run(f"SELECT json_value(NULL, '$.user'), json_value({DOC}, NULL), json_value({DOC}, '$'), "
    "json_value('42', '$'), json_value('\"s\"', '$')")
run("SELECT json_value('[1, [2, 3]]', 1), json_value('[1, 2, 3]', 1)")
run("SELECT json_value('not json', '$.a')")

[(None, None, None, '42', '"s"')]
[(None, '2')]
OSError: Malformed JSON at byte 0 of input: invalid literal.  Input: not json

Examples

The user, the geo object (NULL: it isn't a scalar) and the second element of the array in row 3:

run(f"SELECT id, json_value(payload, '$.user'), json_value(payload, '$.geo'), json_value(payload, '$[1]') "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '"ada"', None, None), (2, '"alan"', None, None), (3, None, None, '20'), (4, None, None, None)]

Compare with a JSON string: the value keeps its quotes, so the constant needs them too:

run(f"SELECT id FROM {TABLE} WHERE json_value(payload, '$.user') = '\"ada\"' ORDER BY id LIMIT 10")

[(1,)]

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 a JSON document as text and can't read one field of it, so json_value is always computed by the Query Engine:

• In the SELECT list, the scan reads the whole payload column and the Query Engine parses each document it returns.
• In WHERE, the scan sends payload for every row, and a FILTER step in the Query Engine parses each document and keeps the rows that match. No rows are skipped in storage.
• Every document it reads must be valid JSON. The first invalid one stops the query with Malformed JSON ... (see invalid JSON in a column).

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE json_value(payload, '$.user') = '\"ada\"'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (json_value(payload, '$  │
│     .user') = '"ada"')    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          payload          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the fields you filter on in their own columns. A comparison of a column with a constant is pushed down to VAST DataBase; the same comparison on a field inside JSON parses every row in the Query Engine.
• Narrow the rows with a pushed-down condition. A condition such as id = 1 is evaluated in VAST DataBase, so a query that fails on the whole table because of one invalid document can still run on the rows it keeps.

Limitations and errors

• Scalars only. To read an object or an array, use json_extract.
• Strings keep their quotes. For plain text, use json_extract_string.
• Invalid JSON stops the query. A pushed-down condition that leaves out the invalid rows lets the query run.
• A list of paths must be constant. A list from a column fails with List of paths must be constant; a list can't contain NULL or a wildcard.
• The document must be text. A number fails with No function matches the given name and argument types, for example 'json_value(INTEGER_LITERAL, STRING_LITERAL)'; a date, a boolean or a list returns NULL, and a BLOB fails with Malformed JSON ....
• The SQL-standard RETURNING clause isn't available. JSON_VALUE(json, path RETURNING BIGINT) is a syntax error. Cast the result instead, or use TRY_CAST(json_extract_string(...) AS BIGINT).
• Other SQL dialects' names aren't available. json_extract_scalar fails with Scalar Function with name json_extract_scalar does not exist!.

run("SELECT json_value(123, '$.a')")
run("SELECT json_extract_scalar('{\"a\": 1}', '$.a')")
run("SELECT TRY_CAST(json_extract_string('{\"a\": 1}', '$.a') AS BIGINT)")

InternalError: Binder Error: No function matches the given name and argument types 'json_value(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
InternalError: Catalog Error: Scalar Function with name json_extract_scalar does not exist!
[(1,)]

Similar functions

run(f"SELECT json_value({DOC}, '$.user'), json_value({DOC}, '$.geo'), json_value({DOC}, '$.tags'), "
    f"json_value({DOC}, '$.z')")
run(f"SELECT json_extract({DOC}, '$.user'), json_extract({DOC}, '$.geo'), json_extract({DOC}, '$.tags'), "
    f"json_extract({DOC}, '$.z')")
run(f"SELECT json_extract_string({DOC}, '$.user'), json_extract_string({DOC}, '$.geo'), "
    f"json_extract_string({DOC}, '$.tags'), json_extract_string({DOC}, '$.z')")

[('"ada"', None, None, None)]
[('"ada"', '{"city":"Oslo","lat":59.9}', '["a","b","c"]', 'null')]
[('ada', '{"city":"Oslo","lat":59.9}', '["a","b","c"]', None)]

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