Welcome to the new DOCS.vastdata.com. We've added new menus and a version selector, plus new persona and resource pages to help customers get started. Looking for a previous article? Use the docs search.

Press CTRL+K to open search

json_extract

Prev Next

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Read values. Returns the value at a path in a JSON document, as JSON text: a string keeps its quotes, and an object or array comes back in compact form. json_extract_path and the operator -> are the same function.

Syntax

json_extract(json, path)
json_extract(json, paths)
json_extract_path(json, path)
json -> path
Argument Type Description
json VARCHAR or JSON The JSON document.
path VARCHAR or an integer A JSON path ('$.geo.city'), a JSON pointer ('/geo/city'), a top-level key ('user'), or an array position (0). It can come from a column.
paths VARCHAR[] A constant list of paths, to read several values in one call.

Returns: JSON, which the driver returns as text (a Python str). A list of paths, or a path with a wildcard, returns JSON[].

Aliases: json_extract_path, 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. 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"'

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 parses the document, follows the path and returns what it finds as JSON text:

Path What it selects Example on DOC Result
'$.key', '$.a.b' a key, a key inside an object '$.geo.city' '"Oslo"'
'$.key[n]' an array element, counting from 0; a negative position, or #-1, counts from the end '$.tags[-1]' '"c"'
'$."a b"' a key with spaces or other special characters, in double quotes a document with the key "a b": 3 '3'
'$.key[*]', '$.*', '$..key' every element, every value, or every key of that name at any depth '$.tags[*]' ['"a"', '"b"', '"c"'] (a list)
'/a/b' a JSON pointer; ~1 stands for / and ~0 for ~ in a key '/geo/city' '"Oslo"'
'key' one top-level key, without $ 'user' '"ada"'
'$' or '' the whole document, in compact form '$' the document without spaces
an integer an array position; a negative one counts from the end json_extract('[1, 2, 3]', -1) '3'

The value comes back as JSON: a string keeps its double quotes, a number is its JSON text, and an object or array is written without the spaces between items. A JSON null value returns the text 'null', not SQL NULL. Keys are case-sensitive. When a key appears twice in an object, the first one is read.

run(f"SELECT json_extract({DOC}, '$.geo.city'), json_extract({DOC}, '$.tags[-1]'), {DOC} -> '$.tags[*]'")
run(f"SELECT json_extract({DOC}, '/geo/city'), json_extract({DOC}, 'user'), json_extract({DOC}, '$')")
run("SELECT json_extract('[1, 2, 3]', 1), json_extract('[1, 2, 3]', -1), json_extract('{\"a\": 1, \"a\": 2}', '$.a')")

[('"Oslo"', '"c"', ['"a"', '"b"', '"c"'])]
[('"Oslo"', '"ada"', '{"user":"ada","n":3,"tags":["a","b","c"],"geo":{"city":"Oslo","lat":59.9},"z":null}')]
[('2', '3', '1')]

NULLs and edge cases

Input Result
A NULL document or a NULL path NULL
A path that isn't in the document ('$.missing', '$.tags[5]', '$.USER') NULL
A key whose value is JSON null ('$.z') 'null'
A path below a JSON null ('$.z.a') NULL
A dotted path without $ ('geo.city') NULL: without $, the text is one key name
'{"a": 1,}' (a trailing comma) '1'
'{"a": NaN}' 'NaN'
'{"a": 1E2}' '100.0'
'not json' Error: Malformed JSON at byte 0 of input: invalid literal. Input: not json
'' (empty text) Error: Malformed JSON at byte 0 of input: input length is 0. Input:
A path that can't be parsed, such as '$[' Error: JSON path error near '['

The same cases, as the cluster returns them:

run(f"SELECT json_extract(NULL, '$.user'), json_extract({DOC}, NULL), json_extract({DOC}, '$.missing'), "
    f"json_extract({DOC}, '$.tags[5]'), json_extract({DOC}, '$.USER')")
run(f"SELECT json_extract({DOC}, '$.z'), json_extract({DOC}, '$.z.a'), json_extract({DOC}, 'geo.city')")
run("SELECT json_extract('{\"a\": 1,}', '$.a'), json_extract('{\"a\": NaN}', '$.a'), json_extract('{\"a\": 1E2}', '$.a')")
run("SELECT json_extract('not json', '$.a')")
run("SELECT json_extract('', '$.a')")
run(f"SELECT json_extract({DOC}, '$[')")

[(None, None, None, None, None)]
[('null', None, None)]
[('1', 'NaN', '100.0')]
OSError: Malformed JSON at byte 0 of input: invalid literal.  Input: not json
OSError: Malformed JSON at byte 0 of input: input length is 0.  Input: 
InternalError: Binder Error: JSON path error near '['

Examples

Read the user, the city and the first tag of each event. -> chains: each step reads from the JSON the previous one returned. On the array in row 3, -> 0 reads the first element:

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

[(1, '"ada"', '"Oslo"', '"a"', None), (2, '"alan"', None, None, None), (3, None, None, None, '10'), (4, None, None, None, None)]

Read two fields in one call with a list of paths. The result is a list with one element per path:

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

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

Convert a value to a SQL type with a cast. A cast of a JSON string to VARCHAR keeps the quotes; to get the text without them, use json_extract_string:

run(f"SELECT ({DOC} -> '$.n')::BIGINT, ({DOC} -> '$.geo.lat')::DOUBLE, ({DOC} -> '$.user')::VARCHAR")

[(3, 59.9, '"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 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 a condition on an extracted value shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE json_extract(payload, '$.geo') IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (json_extract(payload, '$ │
│    .geo') IS NOT NULL)    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         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. The plan for a plain VARCHAR column has no FILTER step:
plan(f"SELECT id FROM {TABLE} WHERE payload = '[10, 20, [30, 40]]'")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
  • 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

  • Invalid JSON stops the query. See the edge cases above. A pushed-down condition that leaves out the invalid rows, such as WHERE id = 1, lets the query run.
  • Put -> in parentheses when you compare it. payload -> '$.user' = '"ada"' is read as payload -> ('$.user' = '"ada"') and fails with No function matches the given name and argument types 'json_extract(VARCHAR, BOOLEAN)'. Write (payload -> '$.user') = '"ada"'.
  • A list of paths must be constant. A list from a column fails with List of paths must be constant; a single path can come from a column. A list can't contain NULL (JSON path cannot be NULL) or a wildcard (Cannot have wildcards in JSON path when supplying multiple paths).
  • The document must be text. A number fails with No function matches the given name and argument types, for example 'json_extract(INTEGER_LITERAL, VARCHAR)', and a BLOB with Malformed JSON .... A date, a boolean or a list returns NULL. Cast other values to VARCHAR first, or build JSON with to_json.
  • The path must be text, a list of text or an integer. A DECIMAL, DATE, BOOLEAN or BLOB path fails with No function matches the given name and argument types, and so does a HUGEINT position.
  • Other SQL dialects' names aren't available. json_extract_scalar and json_query fail with Scalar Function with name ... does not exist!. Use json_extract, or json_extract_string for plain text.
run(f"SELECT id FROM {TABLE} WHERE payload -> '$.user' = '\"ada\"' LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE (payload -> '$.user') = '\"ada\"' LIMIT 10")
run(f"SELECT json_extract({DOC}, ['$.tags[*]', '$.n'])")
run("SELECT json_extract(123, '$.a')")
run("SELECT json_query('{\"a\": [1]}', '$.a')")

InternalError: Binder Error: No function matches the given name and argument types 'json_extract(VARCHAR, BOOLEAN)'. You might need to add explicit type casts.
[(1,)]
InternalError: Binder Error: Cannot have wildcards in JSON path when supplying multiple paths
InternalError: Binder Error: No function matches the given name and argument types 'json_extract(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
InternalError: Catalog Error: Scalar Function with name json_query does not exist!

Similar functions

Function '$.user' (a string) '$.geo' (an object) '$.z' (JSON null) '$.missing'
json_extract, -> '"ada"' '{"city":"Oslo","lat":59.9}' 'null' NULL
json_extract_string, ->> 'ada' '{"city":"Oslo","lat":59.9}' NULL NULL
json_value '"ada"' NULL (scalars only) NULL NULL
json_exists true true true false 
run(f"SELECT json_extract({DOC}, '$.user'), json_extract({DOC}, '$.geo'), json_extract({DOC}, '$.z'), "
    f"json_extract({DOC}, '$.missing')")
run(f"SELECT json_extract_string({DOC}, '$.user'), json_extract_string({DOC}, '$.geo'), "
    f"json_extract_string({DOC}, '$.z'), json_extract_string({DOC}, '$.missing')")
run(f"SELECT json_value({DOC}, '$.user'), json_value({DOC}, '$.geo'), json_value({DOC}, '$.z'), "
    f"json_value({DOC}, '$.missing')")
run(f"SELECT json_exists({DOC}, '$.user'), json_exists({DOC}, '$.geo'), json_exists({DOC}, '$.z'), "
    f"json_exists({DOC}, '$.missing')")

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

To turn a whole document into typed SQL 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

Related articles
  • json_extract_string
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > JSON functions
  • json_value
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > JSON functions
  • json
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > JSON functions