json_merge

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Build JSON. Applies one or more merge patches to a JSON document: keys in a patch are added or replace the document's, nested objects are merged, and a key set to null is removed. Patches are applied from left to right.

Syntax

json_merge_patch(document, patch, ...)

Argument	Type	Description
`document`	`VARCHAR` or `JSON`	The JSON document to change.
`patch`	`VARCHAR` or `JSON`	One or more patches, applied in order.

Returns: JSON. The driver returns it as text (a Python str).

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_merge_patch. 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 user profiles stored as JSON text: two objects, an array (not a profile), and a NULL:

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

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}.profiles"
PATCH = '{"n": null, "geo": {"lat": null}, "seen": true}'
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, doc 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\": []}'), "
    "(3, '[10, 20]'), "
    "(4, NULL)")

[]
[]
[(4,)]

PATCH removes n and the nested geo.lat, and adds seen.

What it does

For each patch in turn:

A key in the patch is added to the document, or replaces the document's value for that key.
A nested object in the patch is merged into the document's object for that key, by the same rules.
A key set to null is removed from the document.
An array in the patch replaces the document's array; arrays aren't merged.
A patch that isn't an object, such as [1] or null, replaces the whole document.

Document	Patch	Result
`{"a": 1}`	`{"b": 2}`	`{"a":1,"b":2}`
`{"a": 1}`	`{"a": null}`	`{}`
`{"a": {"b": 1, "c": 2}}`	`{"a": {"b": null}}`	`{"a":{"c":2}}`
`{"a": [1, 2]}`	`{"a": [3]}`	`{"a":[3]}`
`{"a": 1}`	`[1]`	`[1]`
`{"a": 1}`	`{}`	`{"a":1}`
`{"a": 1}`	`{"b": 2}`, then `{"a": 3}`	`{"b":2,"a":3}`

run("SELECT json_merge_patch('{\"a\": 1}', '{\"b\": 2}'), json_merge_patch('{\"a\": 1}', '{\"a\": null}')")
run("SELECT json_merge_patch('{\"a\": {\"b\": 1, \"c\": 2}}', '{\"a\": {\"b\": null}}'), "
    "json_merge_patch('{\"a\": [1, 2]}', '{\"a\": [3]}')")
run("SELECT json_merge_patch('{\"a\": 1}', '[1]'), json_merge_patch('{\"a\": 1}', '{}'), "
    "json_merge_patch('{\"a\": 1}', '{\"b\": 2}', '{\"a\": 3}')")

[('{"a":1,"b":2}', '{}')]
[('{"a":{"c":2}}', '{"a":[3]}')]
[('[1]', '{"a":1}', '{"b":2,"a":3}')]

NULLs and edge cases

Input	Result
A NULL patch	`NULL`
A NULL document	the patch, as written
A document that isn't an object (an array, a string, a number, `null`)	the patch, as written: its `null` members are kept
`'{}'` with patch `{"a": {"b": null}}`	`{"a":{}}`
Patch `'null'`	`null`
One argument, or none	Error: `json_merge_patch requires at least two parameters`

run("SELECT json_merge_patch('{\"a\": 1}', NULL), json_merge_patch(NULL, '{\"a\": 1, \"b\": null}')")
run("SELECT json_merge_patch('[1]', '{\"a\": 1, \"b\": null}'), json_merge_patch('{}', '{\"a\": {\"b\": null}}'), "
    "json_merge_patch('{\"a\": 1}', 'null')")
run("SELECT json_merge_patch('{\"a\": 1}')")

[(None, '{"a":1,"b":null}')]
[('{"a":1,"b":null}', '{"a":{}}', 'null')]
InternalError: Invalid Input Error: json_merge_patch requires at least two parameters

Examples

Apply PATCH to every profile. The objects in rows 1 and 2 lose n and gain seen. Row 1 also loses geo.lat, and row 2, which has no geo, gains an empty geo object. The array and the NULL row give the patch as written:

run(f"SELECT id, json_merge_patch(doc, '{PATCH}') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '{"user":"ada","tags":["a","b"],"geo":{"city":"Oslo"},"seen":true}'), (2, '{"user":"alan","tags":[],"geo":{},"seen":true}'), (3, '{"n":null,"geo":{"lat":null},"seen":true}'), (4, '{"n":null,"geo":{"lat":null},"seen":true}')]

Patch only the objects, and leave the other rows as they are:

run(f"SELECT id, CASE WHEN json_type(doc) = 'OBJECT' THEN json_merge_patch(doc, '{PATCH}') ELSE doc END "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '{"user":"ada","tags":["a","b"],"geo":{"city":"Oslo"},"seen":true}'), (2, '{"user":"alan","tags":[],"geo":{},"seen":true}'), (3, '[10, 20]'), (4, None)]

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 JSON as text and doesn't parse it, so json_merge_patch is always computed by the Query Engine:

In the SELECT list, the scan reads the whole document and the Query Engine parses and patches it for each row it returns.
In WHERE, a FILTER step in the Query Engine patches the document of every row the scan sends.

plan(f"SELECT id FROM {TABLE} WHERE json_merge_patch(doc, '{PATCH}') IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (json_merge_patch(doc, '{ │
│ "n": null, "geo": {"lat": │
│  null}, "seen": true}') IS│
│          NOT NULL)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            doc            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

Select the rows with a condition on a column. A comparison such as id = 1 is evaluated in VAST DataBase, so only those documents are parsed and patched. The plan has no FILTER step:

plan(f"SELECT json_merge_patch(doc, '{PATCH}') FROM {TABLE} WHERE id = 1")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│ json_merge_patch(doc, '{"n│
│  ": null, "geo": {"lat":  │
│   null}, "seen": true}')  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: doc     │
│                           │
└───────────────────────────┘

Limitations and errors

Text or JSON only. A number, a date, a boolean, a list or a BLOB as the document, or a number as a patch, fails with Invalid Input Error: Arguments to json_merge_patch must be of type VARCHAR or JSON. Convert the value with to_json first.
At least two arguments. json_merge_patch(doc) fails with json_merge_patch requires at least two parameters.
Invalid JSON fails the query with Malformed JSON at byte N of input: .... Filter the rows with json_valid first.
A document that isn't an object is replaced by the patch, null members included. Check the document with json_type if the column can hold arrays or scalars, as in the second example.
Repeated keys in the document are kept, each with its own value.

run("SELECT json_merge_patch(to_json([1, 2]), '{\"a\": 1}')")
run("SELECT json_merge_patch([1, 2], '{\"a\": 1}')")
run("SELECT json_merge_patch('not json', '{}')")

[('{"a":1}',)]
InternalError: Invalid Input Error: Arguments to json_merge_patch must be of type VARCHAR or JSON
OSError: Malformed JSON at byte 0 of input: invalid literal.  Input: not json

Similar functions

Function	What it does	Result
`json_merge_patch('{"a": 1}', '{"b": 2}')`	merges documents	`{"a":1,"b":2}`
json_object`('a', 1, 'b', 2)`	builds an object from pairs	`{"a":1,"b":2}`

run("SELECT json_merge_patch('{\"a\": 1}', '{\"b\": 2}'), json_object('a', 1, 'b', 2)")

[('{"a":1,"b":2}', '{"a":1,"b":2}')]

Clean up:

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

[]
[]

json_merge_patch