json_extract_string

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Read values. Returns the value at a path in a JSON document as plain text: a string without its quotes, a number as its digits. json_extract_path_text and the operator ->> are the same function.

Syntax

json_extract_string(json, path)
json_extract_string(json, paths)
json_extract_path_text(json, path)
json ->> path

Returns: VARCHAR. A list of paths, or a path with a wildcard, returns VARCHAR[].

Aliases: json_extract_path_text, and the operator ->>.

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_extract_string. 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_extract_string"'

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_extract_string reads a value the same way as json_extract, with the same path forms, and returns it as SQL text:

JSON escapes in a string are decoded: "caf\u00e9" in the document comes back as café. The result is always text; to compare it with a number or do arithmetic, cast it.

run(f"SELECT json_extract_string({DOC}, '$.user'), {DOC} ->> '$.n', {DOC} ->> '$.geo', {DOC} ->> '$.z'")
run(f"SELECT json_extract({DOC}, '$.user'), {DOC} -> '$.n', {DOC} -> '$.geo', {DOC} -> '$.z'")
run("SELECT '{\"a\": \"caf\\u00e9\"}' ->> '$.a'")

[('ada', '3', '{"city":"Oslo","lat":59.9}', None)]
[('"ada"', '3', '{"city":"Oslo","lat":59.9}', 'null')]
[('café',)]

NULLs and edge cases

The same cases, as the cluster returns them:

run(f"SELECT json_extract_string(NULL, '$.user'), json_extract_string({DOC}, NULL), {DOC} ->> '$.missing', "
    f"{DOC} ->> '$.tags[5]'")
run(f"SELECT {DOC} ->> '$.z', 'null' ->> '$', '\"s\"' ->> '$', {DOC} ->> '$.tags[*]'")
run("SELECT json_extract_string('not json', '$.a')")
run(f"SELECT json_extract_string({DOC}, '$.user.')")

[(None, None, None, None)]
[(None, None, 's', ['a', 'b', 'c'])]
OSError: Malformed JSON at byte 0 of input: invalid literal.  Input: not json
InternalError: Binder Error: JSON path error near '.'

Examples

The user, the city, and the second element of the array in row 3:

run(f"SELECT id, payload ->> '$.user', json_extract_string(payload, '$.geo.city'), payload ->> 1 "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

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

Keep the rows of one user. Put the ->> expression in parentheses (see Limitations and errors):

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

[(1,)]

Convert a field to a number. TRY_CAST returns NULL where the value isn't a number ("many" in row 2); a plain cast stops the query:

run(f"SELECT id, TRY_CAST(payload ->> '$.n' AS BIGINT) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT ({DOC} ->> '$.n')::BIGINT, TRY_CAST({DOC} ->> '$.user' AS BIGINT)")
run(f"SELECT ({DOC} ->> '$.user')::BIGINT")

[(1, 3), (2, None), (3, None), (4, None)]
[(3, None)]
OSError: Could not convert string 'ada' to INT64

Read several fields in one call. The result is a list, and [1] takes its first element:

run(f"SELECT id, json_extract_string(payload, ['$.user', '$.n']) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT ({DOC} ->> ['$.user', '$.n'])[1]")

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

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_extract_string 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. This holds for ->> too, and for a cast of the result.
• 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 (payload ->> '$.user') = 'ada'")

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

Make it faster

• Store the fields you filter on in their own columns. Extract user when you write the rows and keep it in a VARCHAR column: user_name = 'ada' is pushed down to VAST DataBase, (payload ->> '$.user') = 'ada' 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

• Put ->> in parentheses when you combine it. Without them, id < 5 AND payload ->> '$.user' = 'ada' and id = 1 OR payload ->> '$.user' = 'ada' fail with Could not convert string '...' to BOOL, and ->> '$.n' + 1 on a constant document fails with Could not choose a best candidate function for the function call "+(STRING_LITERAL, INTEGER_LITERAL)". Write (payload ->> '$.user') = 'ada' and (payload ->> '$.n')::BIGINT + 1.
• The result is text. A plain cast of text that isn't a number stops the query, for example Could not convert string 'ada' to INT64. Use TRY_CAST.
• 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; a BLOB fails with Malformed JSON ...; a list returns NULL.
• Other SQL dialects' forms aren't available. jsonb_extract_path_text fails with Scalar Function with name jsonb_extract_path_text does not exist!, and the #>> operator is a syntax error. Use ->> or json_extract_string.

run(f"SELECT id FROM {TABLE} WHERE id < 5 AND payload ->> '$.user' = 'ada' LIMIT 10")
run(f"SELECT {DOC} ->> '$.n' + 1")
run(f"SELECT ({DOC} ->> '$.n')::BIGINT + 1")
run("SELECT jsonb_extract_path_text('{\"a\": 1}', 'a')")

OSError: Could not convert string '{"user": "ada", "n": 3, "tags": ["a", "b"], "geo": {"city": "Oslo", "lat": 59.9}}' to BOOL
InternalError: Binder Error: Could not choose a best candidate function for the function call "+(STRING_LITERAL, INTEGER_LITERAL)". In order to select one, please add explicit type casts.
[(4,)]
InternalError: Catalog Error: Scalar Function with name jsonb_extract_path_text does not exist!

Similar functions

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

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

To convert many fields to typed values in one pass, use json_transform.

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.
• TRY_CAST: convert text to a number without failing.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.