Example 16-1 JSON_EXISTS: Path Expression Without Filter

This example selects purchase-order documents that have a line item whose part description contains a UPC code entry.

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document, '$.LineItems.Part.UPCCode');

Example 16-2 JSON_EXISTS: Current Item and Scope in Path Expression Filters

This example shows three equivalent ways to select documents that have a line item whose part contains a UPC code with a value of 85391628927.

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document,
                    '$?(@.LineItems.Part.UPCCode == 85391628927)');

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document,
                    '$.LineItems?(@.Part.UPCCode == 85391628927)');

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document,
                    '$.LineItems.Part?(@.UPCCode == 85391628927)');

• In the first query, the scope of the filter is the context item, that is, an entire purchase order. @ refers to the context item.

• In the second query, the filter scope is a LineItems array (and each of its elements, implicitly). @ refers to an element of that array.

• In the third query, the filter scope is a Part field of an element in a LineItems array. @ refers to a Part field.

Example 16-3 JSON_EXISTS: Filter Conditions Depend On the Current Item

This example selects purchase-order documents that have both a line item with a part that has UPC code 85391628927 and a line item with an order quantity greater than 3. The scope of each filter, that is, the current item, is in this case the context item. Each filter condition applies independently (to the same document); the two conditions do not necessarily apply to the same line item.

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document,
                    '$?(@.LineItems.Part.UPCCode == 85391628927
                        && @.LineItems.Quantity > 3)');

Example 16-4 JSON_EXISTS: Filter Downscoping

This example looks similar to Example 16-3, but it acts quite differently. It selects purchase-order documents that have a line item with a part that has UPC code and with an order quantity greater than 3. The scope of the current item in the filter is at a lower level; it is not the context item but a LineItems array element. That is, the same line item must satisfy both conditions, for json_exists to return true.

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document,
                    '$.LineItems[*]?(@.Part.UPCCode == 85391628927
                                  && @.Quantity > 3)');

Example 16-5 JSON_EXISTS: Path Expression Using Path-Expression exists Condition

This example shows how to downscope one part of a filter while leaving another part scoped at the document (context-item) level. It selects purchase-order documents that have a User field whose value is "ABULL" and documents that have a line item with a part that has UPC code and with an order quantity greater than 3. That is, it selects the same documents selected by Example 16-4, as well as all documents that have "ABULL" as the user. The argument to path-expression predicate exists is a path expression that specifies particular line items; the predicate returns true if a match is found, that is, if any such line items exist.

(If you use this example or similar with SQL*Plus then you must use SET DEFINE OFF first, so that SQL*Plus does not interpret && exists as a substitution variable and prompt you to define it.)

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document,
                    '$?(@.User == "ABULL"
                        && exists(@.LineItems[*]?(
                                    @.Part.UPCCode == 85391628927
                                    && @.Quantity > 3)))');