doc/modules/cassandra/pages/cql/json.adoc - cassandra - Git at Google

 = 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.
	= 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.