json_contains

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Check and inspect. Returns true if a JSON document contains another JSON value anywhere inside it: a scalar value, an object's key/value pairs, or an array's elements.

Syntax

json_contains(haystack, needle)

Returns: BOOLEAN.

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_contains. 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 stored as VARCHAR. Row 3 holds an array, and row 4 has no values:

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

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, user_name VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '{\"user\": \"ada\", \"n\": 3, \"tags\": [\"a\", \"b\"], \"geo\": {\"city\": \"Oslo\", \"lat\": 59.9}}', 'ada'), "
    "(2, '{\"user\": \"alan\", \"n\": 20, \"tags\": [], \"geo\": null}', 'alan'), "
    "(3, '[10, 20, [30, 40]]', NULL), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

json_contains parses both arguments as JSON and looks for the needle at any depth of the haystack:

String comparison is exact and case-sensitive. A number matches only a number written the same way: 3 doesn't match 3.0.

NULLs and edge cases

The cases of both tables, as the cluster returns them. Each line shows the needle and the result. They use the helpers from Set up the examples:

DOC = '{"user": "ada", "n": 3, "tags": ["a", "b", "c"], "geo": {"city": "Oslo", "lat": 59.9}, "z": null}'
for needle in ['"Oslo"', '3', 'null', '"3"', '3.0', '"ad"', '{"city": "Oslo"}', '{"lat": 59.9, "city": "Oslo"}',
               '{"geo": {"city": "Oslo"}}', '{"city": "oslo"}', '["b", "a"]', '{}', '[]']:
    run(f"SELECT '{needle}', json_contains('{DOC}', '{needle}')")
run("SELECT json_contains(NULL, '1'), json_contains('[1, 2]', NULL), json_contains('[1, 2]', '2'), "
    "json_contains('[[1, 2]]', '[1]'), json_contains('1', '1')")
run(f"SELECT json_contains('{DOC}', 'ada')")
run("SELECT json_contains('not json', '1')")
run("SELECT json_contains(123, '1')")

[('"Oslo"', True)]
[('3', True)]
[('null', True)]
[('"3"', False)]
[('3.0', False)]
[('"ad"', False)]
[('{"city": "Oslo"}', True)]
[('{"lat": 59.9, "city": "Oslo"}', True)]
[('{"geo": {"city": "Oslo"}}', True)]
[('{"city": "oslo"}', False)]
[('["b", "a"]', True)]
[('{}', True)]
[('[]', True)]
[(None, None, True, True, True)]
OSError: Malformed JSON at byte 0 of input: unexpected character.  Input: ada
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_contains(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Which events mention the user ada, a city of Oslo, or the number 20. Row 2 has "n": 20, and row 3 is an array with 20 as one of its elements:

run("SELECT id, json_contains(payload, '\"ada\"'), json_contains(payload, '{\"city\": \"Oslo\"}'), "
    f"json_contains(payload, '20') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, True, True, False), (2, False, False, True), (3, False, False, True), (4, None, None, None)]

Keep the events that have an object with "city": "Oslo":

run("SELECT id FROM " + TABLE + " WHERE json_contains(payload, '{\"city\": \"Oslo\"}') 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 the payload as text and can't look inside it, so json_contains always runs in the Query Engine and parses the document of every row it gets:

• In the SELECT list, the scan reads the column and the Query Engine computes json_contains for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine parses each document and 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_contains(payload, '\"ada\"')")

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

Make it faster

• Keep the values you search for in their own columns. A comparison of a plain column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan for user_name = 'ada' has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE user_name = 'ada'")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Compare the whole document when you can. payload = '[]' is pushed down too; payload LIKE '%ada%' isn't, and runs in a FILTER step like json_contains.
• Write each document once, valid. Every row is parsed on every query; see Make JSON queries faster.

Limitations and errors

• The needle is JSON, not a plain value. Write a string with its double quotes, '"ada"'. Without them the needle isn't valid JSON and the query fails with Malformed JSON at byte 0 of input: unexpected character. to_json turns a SQL value into JSON text: to_json('ada') returns "ada".
• One invalid document fails the query. If any row the function reads isn't valid JSON, the query stops with Malformed JSON at byte .... Keep such rows out with a pushed-down condition: see invalid JSON in a column.
• Text or JSON arguments. A number fails with No function matches the given name and argument types; cast it, or write it as text.
• It doesn't say where the value is. To test one path, use json_extract_string or json_exists.

Similar functions

The four tests and json_contains on each row:

run("SELECT id, json_contains(payload, '\"ada\"'), json_exists(payload, '$.geo'), "
    "list_contains(json_keys(payload), 'geo'), json_extract_string(payload, '$.user') = 'ada', "
    f"payload LIKE '%ada%' FROM {TABLE} ORDER BY id LIMIT 10")

[(1, True, True, True, True, True), (2, False, True, True, False, False), (3, False, False, False, None, False), (4, None, None, None, None, 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, JSON paths and invalid JSON.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.