json_array_length

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Check and inspect. Returns the number of elements in a JSON array, or in the array at a path in a document. For an object, a scalar or JSON null, it returns 0.

Syntax

json_array_length(json)
json_array_length(json, path)
json_array_length(json, paths)

Argument	Type	Description
`json`	`VARCHAR` or `JSON`	The JSON document.
`path`	`VARCHAR`	A JSON path, JSON pointer or top-level key, to count an inner array.
`paths`	`VARCHAR[]`	A constant list of paths.

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

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

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_array_length parses the document, follows the path if you give one, and counts the top-level elements of the array it finds. A nested array counts as one element, and so does a null element. Anything that isn't an array (an object, a string, a number, a boolean or JSON null) counts 0.

run(f"SELECT json_array_length({DOC}, '$.tags'), json_array_length({DOC}, '$.geo'), json_array_length({DOC})")
run("SELECT json_array_length('[1, [2, 3], null]'), json_array_length('[[], [], []]'), json_array_length('[]')")

[(3, 0, 0)]
[(3, 3, 0)]

NULLs and edge cases

Input	Result
A NULL document or a NULL path	`NULL`
A path that isn't in the document (`'$.missing'`)	`NULL`
A path to JSON `null` (`'$.z'`)	`0`
A bare scalar document: `'"s"'`, `'42'`, `'true'`, `'null'`	`0`
A wildcard path, `'$.arr[*]'` on `{"arr": [[1, 2], [3, 4]]}`	one count per match: `[2, 2]`
A list of paths	one count per path, `NULL` for a missing one
`'not json'`	Error: `Malformed JSON at byte 0 of input: invalid literal. Input: not json`
An integer path, `json_array_length(json, 0)`	Error: `No function matches the given name and argument types`; write `'$[0]'`

The same cases, as the cluster returns them:

run(f"SELECT json_array_length(NULL), json_array_length({DOC}, NULL), json_array_length({DOC}, '$.missing'), "
    f"json_array_length({DOC}, '$.z')")
run("SELECT json_array_length('\"s\"'), json_array_length('42'), json_array_length('true'), json_array_length('null')")
run("SELECT json_array_length('{\"arr\": [[1, 2], [3, 4]]}', '$.arr[*]')")
run(f"SELECT json_array_length({DOC}, ['$.tags', '$.geo', '$.missing'])")
run("SELECT json_array_length('not json')")
run(f"SELECT json_array_length({DOC}, 0)")

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

Examples

The length of each document and of its tags array. The objects count 0; the array in row 3 has three elements, one of them an array:

run(f"SELECT id, json_array_length(payload), json_array_length(payload, '$.tags') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 0, 2), (2, 0, 0), (3, 3, None), (4, None, None)]

Keep the events that have at least one tag:

run(f"SELECT id FROM {TABLE} WHERE json_array_length(payload, '$.tags') > 0 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 look inside it, so json_array_length 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_array_length(payload, '$.tags') > 0")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (json_array_length(payload│
│      , '$.tags') > 0)     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          payload          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

Store arrays as list columns. A VARCHAR[] column holds the tags as a SQL list, which needs no JSON parsing; count its elements with array_length.
Store the count if you filter on it often. A BIGINT column such as tag_count, set when you write the row, is evaluated in VAST DataBase; json_array_length(payload, '$.tags') > 0 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

Top-level elements only. Nested arrays aren't flattened: '[[1, 2], [3]]' has 2 elements.
0 doesn't mean an empty array. An object or a scalar also returns 0. Check json_type first if the difference matters.
Paths are text. There's no integer form; write an array position as '$[0]'.
Invalid JSON stops the query. Use json_valid to find invalid documents first.
A list of paths must be constant. A list from a column fails with List of paths must be constant.
The document must be text. A number fails with No function matches the given name and argument types, for example 'json_array_length(INTEGER_LITERAL)'; a BLOB fails with Malformed JSON ....

run("SELECT json_array_length('[[1, 2], [3]]'), json_array_length('{\"a\": 1}'), json_type('{\"a\": 1}')")
run("SELECT json_array_length(123)")

[(2, 0, 'OBJECT')]
InternalError: Binder Error: No function matches the given name and argument types 'json_array_length(INTEGER_LITERAL)'. You might need to add explicit type casts.

Similar functions

Function	Counts	`'[1, "a"]'`	`'{"a": 1}'`
`json_array_length`	elements of a JSON array	`2`	`0`
json_keys	lists the keys of a JSON object	`[]`	`['a']`
json_type	names the type	`'ARRAY'`	`'OBJECT'`
array_length	elements of a SQL list, not JSON text	(lists only)	(lists only)

run("SELECT json_array_length('[1, \"a\"]'), json_keys('[1, \"a\"]'), json_type('[1, \"a\"]')")
run("SELECT json_array_length('{\"a\": 1}'), json_keys('{\"a\": 1}'), json_type('{\"a\": 1}')")
run("SELECT array_length([1, 2])")

[(2, [], 'ARRAY')]
[(0, ['a'], 'OBJECT')]
[(2,)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]