13.1 Use Bind Variables With JSON_TRANSFORM

As a general rule, use bind variables with Oracle SQL function json_transform, for better performance and more flexibility.

When essentially the same query or update statement is repeated, with only some literal values in its textual representation being different, that code needs to be recompiled, which can be expensive. This is especially the case if the same code runs repeatedly, and only some values in comparisons or assignments change. It's generally a good idea to instead use bind variables in place of such literal values.

Bind variables are often used in programming languages. A Java JDBC setter function might use variable that's bound to a prepared statement, for example. We show an example here that uses SQL bind variables to transmit values to a SQL UPDATE statement.

Note:

The same reasons to use bind variables, and the same PASSING clause syntax, apply when using bind variables with SQL/JSON condition json_exists.

Example 13-5 Using SQL Bind Variables with JSON_TRANSFORM

CREATE JSON COLLECTION TABLE customers;

INSERT INTO customers VALUES ('{"_id"    : 1234,
                                "name"   : "Jane Doe",
                                "email"  : "jane@example.com",
                                "tags"   : [ "newsletter" ],
                                "status" : "gold",
                                "joined" : "2024"}');

This UPDATE statement adds the tag "free shipping" to all gold-status customers who joined before 2025: 


UPDATE customers c
  SET c.data =
      json_transform(c.data,
                     APPEND '$?(@status == "gold").tags' = 'free shipping')
  WHERE json_exists(c.data, '$?(@joined < 2025)');

That operation adds a certain tag ("free shipping") to customers who have a given status ("gold") and who joined before a given year (2025). Those specific values, tag, status, and year, are values that are likely to change, while the basic update operation remains the same.

But if a very similar UPDATE is made, just to add a different tag ("loyalty discount") to customers with a different status ("silver") who joined before a different year (2026), then what is essentially the same code needs to be recompiled, because its textual representation has changed.

Instead of using such a specific update statement, we use the basic operation as a template, abstracting out the simple pieces that we think might change, substituting bind variables for them. In SQL Developer the code defining the bind variables looks like this:

VAR status VARCHAR2(20);
exec :status = 'gold';

VAR tag VARCHAR2(20);
exec :tag = 'free shipping';

VAR year NUMBER;
exec :year = 2025;

The UPDATE statement using those bind variables is more generic than the one using literal values — the compiled code that results from that textual template can accommodate different values for the variables. 

UPDATE customers c
           SET c.data =
               json_transform(c.data,
                              APPEND '$?(@status == $statusVar).tags' = ':tag'
                              PASSING :status AS 'statusVar')
           WHERE json_exists(c.data, '$?(@joined < $yearVar)
                             PASSING :year AS 'yearVar')');

SQL bind variable :tag is used in a SQL expression (the literal string 'tag') as the RHS (right-hand side) of an APPEND assignment operation. When the SQL expression is evaluated, the resulting value is the value of the bind variable. (It's then implicitly converted from a SQL string to a JSON string by the assignment.)

SQL bind variables :status and :year are passed to json_transform and json_exists as SQL/JSON variables statusVar and yearVar, respectively, where they're used in SQL/JSON path expressions.

Related Topics

Parent topic: Oracle SQL Function JSON_TRANSFORM