Special-Criterion Clause (Reference)

The script content on this page is for navigation purposes only and does not alter the content in any way.

A special criterion clause is a spatial clause (with operator $near, $intersects, or $within), or a contains clause (with operator $contains).

Like an ID clause or a text-contains clause, you can use a special-criterion clause only in the outermost condition of a filter, that is, in a condition used in a composite filter or in a filter-condition filter.

More precisely, if filter also uses other operators, in addition to the operators for a special-criterion clause, then its outermost condition must have operator $and, and the special-criterion clauses must be members of elements in the array argument to that $and occurrence.

Related Topics

Spatial Clause (Reference)

GeoJSON objects are JSON objects that represent geographic data. You can use a SODAfilter spatial clause to match GeoJSON geometry objects in your documents.

Note: To use filter spatial operators you need Oracle Database Release 12c (12.2.0.1) or later.

A spatial filter clause is a field followed by an object with a spatial operator: $near, $intersects, or $within. It matches the field only if it contains GeoJSON geographic data that is near a specified position, intersects a specified geometric object, or is within a specified geometric object, respectively.

Each of the spatial filter operators is followed by a JSON object whose fields must include $geometry. Operator $near must also include field $distance, and it can include $unit. A compile-time error is raised if $geometry is missing or if $distance or $unit is present with operator $intersects or $within.

The value of field $geometry is interpreted as a GeoJSON geometry object (other than a geometry collection), such as a point or a polygon. Each such object has a type field, with the geometry type, such as "Point" or "Polygon" as value, and a coordinates field, which defines the shape and location of the object, respectively.

(For a single position, such as an object of type "Point", field coordinates is an array of numbers, the first three of which generally represent longitude, latitude, and altitude, in that order.)

The value of field $distance must be a positive number, the distance from the field preceding spatial operator $near to the geometry object specified by $geometry. For non-point geometry objects, such as lines and polygons, the distance is the minimum distance between them. The distance between two adjacent polygons is zero.

The value of field $unit is a string such as "mile" that specifies the GeoJSON unit to use for the $distance value. The available units are defined in database table SDO_UNITS_OF_MEASURE. The default unit is "mile".

Example 5-4filter With a Spatial Clause

This example matches a location field whose value is GeoJSON geometry object of type Point, and which is within 60 miles of the coordinates [-122.417, 37.783] (San Francisco, California). It would match data with a "location" value of [-122.236, 37.483] (Redwood City, California). (Note that the first element of array "coordinates" is the longitude, and the second is the latitude.)

{"location" : { "$near" : { "$geometry" : { "type" : "Point",
                                            "coordinates" :
                                              [-122.417, 37.783] },
                            "$distance" : 60,
                            "$unit"     : "mile" } } }

The default error-handling behavior for a filter spatial clause is that the targeted field need not be present, but if it is present then its value must be a single GeoJSON geometry object. An error is raised at query time if, for any matching document, that is not the case.1

A spatial clause can specify an alternative error-handling behavior from the default by including one of the following Boolean fields with a true value in the object that a spatial operator ($near, $within, $intersects) applies to. (Only one of these error-handling fields can be specified as true; otherwise, a syntax error is raised at query time.)

Note:

If you have created a SODA spatial index for a field whose value is a GeoJSON geometry object, and if you use a filter that targets that field, then the index can be picked up for the filter only if both index and filter specify the same error-handling behavior for that field. Both must specify the same one of these:

Related Topics

See Also:

Contains Clause (Reference)

A contains clause is a field followed by an object with one $contains operator, whose value is a string. It matches a JSON document only if a string or number in the field value matches the string operand somewhere, including in array elements. Matching is Oracle Text full-text.

The string operand is matched as a full word or number against strings and numbers in the field value, including in array elements.

For example, $contains operand "beth" matches the string "Beth Smith", but not the string "Elizabeth Smith". Operand "10" matches the number 10 or the string "10 Main Street", but not the number 110 or the string "102 Main Street".

Note: To use operator $contains you need Oracle Database Release 12c (12.2.0.1) or later.

Oracle Text technology underlies SODA filter operator $contains. This means, for instance, that you can query for text that is near some other text, or query use fuzzy pattern-matching.

For details about the behavior of a SODA filter contains clause see the Oracle Database documentation for SQL condition json_textcontains.

To be able to use operator $contains you first must create a JSON search index; otherwise, a filter with $contains raises a SQL error.

You can use a contains clause only in the outermost condition of a filter. You can have multiple contains clauses at the top level, provided their fields are different (objects in filters must not have duplicate fields).

For example, this filter checks for a "name" field that contains the word "beth" (case-insensitively) and an "address" field that contains the number 10 or the string "10" as a word:

{ "name"    : { "$contains" : "beth" },
  "address" : { "$contains" : "10" } }

To have the effect of multiple contains clauses for the same field (search the same field for multiple word or number patterns), the outermost condition must have operator $and, and the contains clauses must occur in object elements of the array argument to that $and occurrence.

For example, this filter checks for an "address" field that contains both the word "street" and either the number 10 or the word "10":

{"$and" : [ { "address" : { "$contains" : "street" } },
            { "address" : { "$contains" : "10" } } ] }

Related Topics

See Also:

  1. The default error-handling behavior corresponds to to the use of SQL clauses ERROR ON ERROR and NULL ON EMPTY for a json_value expression. ↩

  2. A true value of $scalarRequired corresponds to the use of SQL clause ERROR ON ERROR for a json_value expression. ↩

  3. A true value of $lax corresponds to the use of SQL clause NULL ON ERROR for a functional index created on a json_value expression. ↩