3 Query JSON Data in TimesTen
To query particular JSON fields, or to map particular JSON fields to SQL columns, you can
use the SQL/JSON path language. In its simplest form a path expression consists of one
or more field names separated by periods (.). More complex path
expressions can contain filters and array indexes.
TimesTen provides two ways of querying JSON data:
-
A dot-notation syntax, which is essentially a table alias, followed by a JSON column name, followed by one or more field names—all separated by periods (
.). An array step can follow each of the field names. This syntax is designed to be simple to use and to return JSON values whenever possible. -
SQL/JSON functions and conditions, which completely support the path language and provide more power and flexibility than dot-notation syntax. You can use them to create, query, and operate on JSON data stored in a TimesTen database.
-
The
JSON_EXISTScondition tests for the existence of a particular value within some JSON data. -
The
JSON_VALUEfunction selects a scalar value from some JSON data, as a SQL value. -
The
JSON_QUERYfunction selects one or more values from some JSON data, as a SQL string representing the JSON values. It is used especially to retrieve fragments of a JSON document, typically a JSON object or array. -
The
JSON_TABLEfunction projects some JSON data as a virtual table, which you can also think of as an inline view.
-
Because the path language is part of the query language, no fixed schema is imposed on the data. This design supports schemaless development. A schema, in effect, gets defined at query time, by your specifying a given path. This is in contrast to the more usual approach with SQL of defining a schema (a set of table rows and columns) for the data at storage time.
The JSON_EQUAL condition does not accept a path-expression
argument. It just compares two JSON values and returns true if they are equal, false
otherwise. For this comparison, insignificant whitespace and insignificant object member
order are ignored. For example, JSON objects are equal if they have the same members,
regardless of their order.