| = JSON Support |
| |
| Cassandra 2.2 introduces JSON support to `SELECT <select-statement>` and |
| `INSERT <insert-statement>` statements. |
| This support does not fundamentally alter the CQL API (for example, the schema is still |
| enforced). |
| It simply provides a convenient way to work with JSON documents. |
| |
| == SELECT JSON |
| |
| With `SELECT` statements, the `JSON` keyword is used to return each row as a single `JSON` encoded map. |
| The remainder of the `SELECT` statement behavior is the same. |
| |
| The result map keys match the column names in a normal result set. |
| For example, a statement like `SELECT JSON a, ttl(b) FROM ...` would result in a map with keys `"a"` and `"ttl(b)"`. |
| However, there is one notable exception: for symmetry with `INSERT JSON` behavior, case-sensitive column names with upper-case letters will be surrounded with double quotes. |
| For example, `SELECT JSON myColumn FROM ...` would result in a map key `"\"myColumn\""` with escaped quotes). |
| |
| The map values will JSON-encoded representations (as described below) of the result set values. |
| |
| == INSERT JSON |
| |
| With `INSERT` statements, the new `JSON` keyword can be used to enable |
| inserting a `JSON` encoded map as a single row. The format of the `JSON` |
| map should generally match that returned by a `SELECT JSON` statement on |
| the same table. In particular, case-sensitive column names should be |
| surrounded with double quotes. For example, to insert into a table with |
| two columns named "myKey" and "value", you would do the following: |
| |
| [source,cql] |
| ---- |
| include::example$CQL/insert_json.cql[] |
| ---- |
| |
| By default (or if `DEFAULT NULL` is explicitly used), a column omitted |
| from the `JSON` map will be set to `NULL`, meaning that any pre-existing |
| value for that column will be removed (resulting in a tombstone being |
| created). Alternatively, if the `DEFAULT UNSET` directive is used after |
| the value, omitted column values will be left unset, meaning that |
| pre-existing values for those column will be preserved. |
| |
| == JSON Encoding of Cassandra Data Types |
| |
| Where possible, Cassandra will represent and accept data types in their |
| native `JSON` representation. Cassandra will also accept string |
| representations matching the CQL literal format for all single-field |
| types. For example, floats, ints, UUIDs, and dates can be represented by |
| CQL literal strings. However, compound types, such as collections, |
| tuples, and user-defined types must be represented by native `JSON` |
| collections (maps and lists) or a JSON-encoded string representation of |
| the collection. |
| |
| The following table describes the encodings that Cassandra will accept |
| in `INSERT JSON` values (and `fromJson()` arguments) as well as the |
| format Cassandra will use when returning data for `SELECT JSON` |
| statements (and `fromJson()`): |
| |
| [cols=",,,",options="header",] |
| |=== |
| |Type |Formats accepted |Return format |Notes |
| |
| | `ascii` | string | string | Uses JSON's `\u` character escape |
| |
| | `bigint` | integer, string | integer | String must be valid 64 bit integer |
| |
| | `blob` | string | string | String should be 0x followed by an even number of hex digits |
| |
| | `boolean` | boolean, string | boolean | String must be "true" or "false" |
| |
| | `date` | string | string | Date in format `YYYY-MM-DD`, timezone UTC |
| |
| | `decimal` | integer, float, string | float | May exceed 32 or 64-bit IEEE-754 floating point precision in client-side decoder |
| |
| | `double` | integer, float, string | float | String must be valid integer or float |
| |
| | `float` | integer, float, string | float | String must be valid integer or float |
| |
| | `inet` | string | string | IPv4 or IPv6 address |
| |
| | `int` | integer, string | integer | String must be valid 32 bit integer |
| |
| | `list` | list, string | list | Uses JSON's native list representation |
| |
| | `map` | map, string | map | Uses JSON's native map representation |
| |
| | `smallint` | integer, string | integer | String must be valid 16 bit integer |
| |
| | `set` | list, string | list | Uses JSON's native list representation |
| |
| | `text` | string | string | Uses JSON's `\u` character escape |
| |
| | `time` | string | string | Time of day in format `HH-MM-SS[.fffffffff]` |
| |
| | `timestamp` | integer, string | string | A timestamp. Strings constant allows to input `timestamps |
| as dates <timestamps>`. Datestamps with format `YYYY-MM-DD HH:MM:SS.SSS` |
| are returned. |
| |
| | `timeuuid` | string | string | Type 1 UUID. See `constant` for the UUID format |
| |
| | `tinyint` | integer, string | integer | String must be valid 8 bit integer |
| |
| | `tuple` | list, string | list | Uses JSON's native list representation |
| |
| | `UDT` | map, string | map | Uses JSON's native map representation with field names as keys |
| |
| | `uuid` | string | string | See `constant` for the UUID format |
| |
| | `varchar` | string | string | Uses JSON's `\u` character escape |
| |
| | `varint` | integer, string | integer | Variable length; may overflow 32 or 64 bit integers in client-side decoder |
| |=== |
| |
| == The fromJson() Function |
| |
| The `fromJson()` function may be used similarly to `INSERT JSON`, but |
| for a single column value. It may only be used in the `VALUES` clause of |
| an `INSERT` statement or as one of the column values in an `UPDATE`, |
| `DELETE`, or `SELECT` statement. For example, it cannot be used in the |
| selection clause of a `SELECT` statement. |
| |
| == The toJson() Function |
| |
| The `toJson()` function may be used similarly to `SELECT JSON`, but for |
| a single column value. It may only be used in the selection clause of a |
| `SELECT` statement. |