Skip to content

Builtin Functions for JSON documents

Tian Xia edited this page Aug 25, 2016 · 1 revision

We also introduced several JSON built-in functions, which work on blob, text, and document columns.

  • JSON_VALID
  • JSON_EXTRACT
  • JSON_EXTRACT_VALUE
  • JSON_CONTAINS_KEY
  • JSON_ARRAY_LENGTH
  • JSON_CONTAINS

JSON_VALID

Syntax

JSON_VALID (column_name)

Description

Returns true if the first parameter is a valid json doc, or otherwise returns false.

JSON_EXTRACT

Syntax

JSON_EXTRACT (column_name, key1[, key2, ...])

Description

Given the key path (key1, ...), it returns the corresponding value in JSON format.

JSON_EXTRACT_VALUE

Syntax

JSON_EXTRACT_VALUE (column_name, key1[, key2, ...])

Description

Similar to json_extract, except that some primitive values may not be in JSON format.

JSON_CONTAINS_KEY

Syntax

JSON_CONTAINS_KEY (column_name, key1[, key2, ...])

Description

Returns true if the key path exists, or otherwise false.

JSON_ARRAY_LENGTH

Syntax

JSON_ARRAY_LENGTH (column_name)

Description

Returns the size of the json array. NULL if the string is not a valid json array type.

JSON_CONTAINS

Syntax

JSON_CONTAINS (column_name, key, [value])

Description

Returns true if the specified key-value pair is found on any level of the JSON document in the first parameter. The value is an optional argument; if it is omitted, the function just searches for the existence of the key in the document.

Clone this wiki locally