13.14 JSON_TRANSFORM Operator REMOVE

JSON_TRANSFORM operator REMOVE removes all parts of the input data that are targeted by at least one of the specified path expressions.

An error is raised if you try to remove all of the data; that is, you cannot use REMOVE '$'. By default, no error is raised if the targeted data does not exist (IGNORE ON MISSING).

Example 13-25 JSON_TRANFORM: Removing an Array Element by Position

This code removes the first element (index 0) from the input array, [0,1,2].

SELECT json_transform('{"a":[0,1,2]}',
                      REMOVE '$.a[0]')

Result:

{"a":[ 1,2 ]}

Example 13-26 JSON_TRANSFORM: Removing Array Elements That Satisfy a Predicate

This example removes all line items whose part UPCCode value is 85391628927. These are the LineItems array elements that are objects with a field Part, which is itself an object with a field UPCCode of value 85391628927. It does nothing (no error is raised) if the targeted data doesn't exist, because IGNORE ON MISSING is the default handler.

json_transform(data,
               REMOVE '$.LineItems[*]?(@.Part.UPCCode == 85391628927)')

Example 13-27 JSON_TRANFORM: Removing a Field That Satisfies a Predicate

This code removes top-level field (array) LineItems altogether, if it has an element that's an object with a field Part, which is itself an object with a field UPCCode of value 85391628927. It does nothing (no error is raised) if the targeted data doesn't exist, because IGNORE ON MISSING is the default handler.

json_transform(data,
               REMOVE '$.LineItems?(@.Part.UPCCode == 85391628927)')

Note that the only difference between Example 13-27 and Example 13-26 is the presence or absence of [*]:

• With [*], only the line-item array elements where the predicate holds are removed.

• Without [*], an entire LineItems array is removed if it has an element where the predicate holds.

Operator REMOVE is an exception, in that it can be immediately followed by more than one path expression. It is not an assignment operation (no = and RHS).

You can think of operators SET (with handler CREATE ON MISSING) and INSERT (but not KEEP) as inverses of operator REMOVE.

Note:

JSON_TRANSFORM operators REMOVE and KEEP have opposite effect, in this sense:

• REMOVE removes input data that's targeted by any of the specified path expressions.

  Put in terms of keeping, REMOVE keeps input data that's not targeted by any of the path expressions.

• KEEP keeps (only) input data that's targeted by any of the specified path expressions.

  Put in terms of removal, KEEP removes input data that's not targeted by any of the path expressions.

Be aware of this gotcha: You can sequentially use REMOVE, but not KEEP, to incrementally remove more and more data.

For example, suppose this is the input data: {"b":"bird", "c":"cat", "d":"dog", "h":"horse"}.

REMOVE '$.c' // Result: {"b":"bird", "d":"dog", "h":"horse"}
REMOVE '$.d' // Result: {"b":"bird", "h":"horse"}

KEEP '$.b' // Result: {"b":"bird"}
KEEP '$.h' // No field h to keep. By default (IGNORE ON MISSING), no error is raised.

To keep data that matches different path expressions, you need to use those expressions together, in the same KEEP operation:

KEEP '$.b', '$.h' // Result: {"b":"bird", "h":"horse"}

(To sequentially keep less and less data, you need to specify smaller and smaller subsets of path expressions at each KEEP invocation, which isn't convenient/practical.)

These are the handlers allowed for operator REMOVE:

• REMOVE ON EXISTING (default).

• IGNORE ON MISSING (default), ERROR ON MISSING.