系统（内置）函数 #

• numeric

• numeric

返回给定时间列属性 rowtime 的当前水印，如果管道中的当前操作没有可用的上游操作的公共水印时则为 NULL。 函数的返回类型被推断为与提供的时间列属性匹配，但调整后的精度为 3。例如时间列属性为 TIMESTAMP_LTZ(9)，则函数将返回 TIMESTAMP_LTZ(3)。

请注意，此函数可以返回 NULL，您可能必须考虑这种情况。例如，如果您想过滤掉后期数据，您可以使用：

WHERE
  CURRENT_WATERMARK(ts) IS NULL
  OR ts > CURRENT_WATERMARK(ts)

Determine whether a given string is valid JSON.

Specifying the optional type argument puts a constraint on which type of JSON object is allowed. If the string is valid JSON, but not that type, false is returned. The default is VALUE.

-- TRUE
'1' IS JSON
'[]' IS JSON
'{}' IS JSON

-- TRUE
'"abc"' IS JSON
-- FALSE
'abc' IS JSON
NULL IS JSON

-- TRUE
'1' IS JSON SCALAR
-- FALSE
'1' IS JSON ARRAY
-- FALSE
'1' IS JSON OBJECT

-- FALSE
'{}' IS JSON SCALAR
-- FALSE
'{}' IS JSON ARRAY
-- TRUE
'{}' IS JSON OBJECT

Determines whether a JSON string satisfies a given path search criterion.

If the error behavior is omitted, FALSE ON ERROR is assumed as the default.

-- TRUE
SELECT JSON_EXISTS('{"a": true}', '$.a');
-- FALSE
SELECT JSON_EXISTS('{"a": true}', '$.b');
-- TRUE
SELECT JSON_EXISTS('{"a": [{ "b": 1 }]}',
  '$.a[0].b');

-- TRUE
SELECT JSON_EXISTS('{"a": true}',
  'strict $.b' TRUE ON ERROR);
-- FALSE
SELECT JSON_EXISTS('{"a": true}',
  'strict $.b' FALSE ON ERROR);

Serializes a value into JSON.

This function returns a JSON string containing the serialized value. If the value is NULL, the function returns NULL.

-- NULL
JSON_STRING(CAST(NULL AS INT))

-- '1'
JSON_STRING(1)
-- 'true'
JSON_STRING(TRUE)
-- '"Hello, World!"'
JSON_STRING('Hello, World!')
-- '[1,2]'
JSON_STRING(ARRAY[1, 2])

Extracts a scalar from a JSON string.

This method searches a JSON string for a given path expression and returns the value if the value at that path is scalar. Non-scalar values cannot be returned. By default, the value is returned as STRING. Using returningType a different type can be chosen, with the following types being supported:

• VARCHAR / STRING
• BOOLEAN
• INTEGER
• DOUBLE

For empty path expressions or errors a behavior can be defined to either return null, raise an error or return a defined default value instead. When omitted, the default is NULL ON EMPTY or NULL ON ERROR, respectively. The default value may be a literal or an expression. If the default value itself raises an error, it falls through to the error behavior for ON EMPTY, and raises an error for ON ERROR.

For path contains special characters such as spaces, you can use ['property'] or ["property"] to select the specified property in a parent object. Be sure to put single or double quotes around the property name. When using JSON_VALUE in SQL, the path is a character parameter which is already single quoted, so you have to escape the single quotes around property name, such as JSON_VALUE('{"a b": "true"}', '$.[''a b'']').

-- "true"
JSON_VALUE('{"a": true}', '$.a')

-- TRUE
JSON_VALUE('{"a": true}', '$.a' RETURNING BOOLEAN)

-- "false"
JSON_VALUE('{"a": true}', 'lax $.b'
    DEFAULT FALSE ON EMPTY)

-- "false"
JSON_VALUE('{"a": true}', 'strict $.b'
    DEFAULT FALSE ON ERROR)

-- 0.998D
JSON_VALUE('{"a.b": [0.998,0.996]}','$.["a.b"][0]' 
    RETURNING DOUBLE)

-- "right"
JSON_VALUE('{"contains blank": "right"}', 'strict $.[''contains blank'']' NULL ON EMPTY DEFAULT 'wrong' ON ERROR)

Extracts JSON values from a JSON string.

The result is always returned as a STRING. The RETURNING clause is currently not supported.

The wrappingBehavior determines whether the extracted value should be wrapped into an array, and whether to do so unconditionally or only if the value itself isn’t an array already.

onEmpty and onError determine the behavior in case the path expression is empty, or in case an error was raised, respectively. By default, in both cases null is returned. Other choices are to use an empty array, an empty object, or to raise an error.

-- '{ "b": 1 }'
JSON_QUERY('{ "a": { "b": 1 } }', '$.a')
-- '[1, 2]'
JSON_QUERY('[1, 2]', '$')
-- NULL
JSON_QUERY(CAST(NULL AS STRING), '$')
-- '["c1","c2"]'
JSON_QUERY('{"a":[{"c":"c1"},{"c":"c2"}]}',
    'lax $.a[*].c')

-- Wrap result into an array
-- '[{}]'
JSON_QUERY('{}', '$' WITH CONDITIONAL ARRAY WRAPPER)
-- '[1, 2]'
JSON_QUERY('[1, 2]', '$' WITH CONDITIONAL ARRAY WRAPPER)
-- '[[1, 2]]'
JSON_QUERY('[1, 2]', '$' WITH UNCONDITIONAL ARRAY WRAPPER)

-- Scalars must be wrapped to be returned
-- NULL
JSON_QUERY(1, '$')
-- '[1]'
JSON_QUERY(1, '$' WITH CONDITIONAL ARRAY WRAPPER)

-- Behavior if path expression is empty / there is an error
-- '{}'
JSON_QUERY('{}', 'lax $.invalid' EMPTY OBJECT ON EMPTY)
-- '[]'
JSON_QUERY('{}', 'strict $.invalid' EMPTY ARRAY ON ERROR)

Builds a JSON object string from a list of key-value pairs.

Note that keys must be non-NULL string literals, while values may be arbitrary expressions.

This function returns a JSON string. The ON NULL behavior defines how to treat NULL values. If omitted, NULL ON NULL is assumed by default.

Values which are created from another JSON construction function call (JSON_OBJECT, JSON_ARRAY) are inserted directly rather than as a string. This allows building nested JSON structures.

-- '{}'
JSON_OBJECT()

-- '{"K1":"V1","K2":"V2"}'
JSON_OBJECT('K1' VALUE 'V1', 'K2' VALUE 'V2')

-- Expressions as values
JSON_OBJECT('orderNo' VALUE orders.orderId)

-- ON NULL
JSON_OBJECT(KEY 'K1' VALUE CAST(NULL AS STRING) NULL ON NULL)   -- '{"K1":null}'
JSON_OBJECT(KEY 'K1' VALUE CAST(NULL AS STRING) ABSENT ON NULL) -- '{}'

-- '{"K1":{"K2":"V"}}'
JSON_OBJECT(
  KEY 'K1'
  VALUE JSON_OBJECT(
    KEY 'K2'
    VALUE 'V'
  )
)

Builds a JSON array string from a list of values.

This function returns a JSON string. The values can be arbitrary expressions. The ON NULL behavior defines how to treat NULL values. If omitted, ABSENT ON NULL is assumed by default.

Elements which are created from another JSON construction function call (JSON_OBJECT, JSON_ARRAY) are inserted directly rather than as a string. This allows building nested JSON structures.

-- '[]'
JSON_ARRAY()
-- '[1,"2"]'
JSON_ARRAY(1, '2')

-- Expressions as values
JSON_ARRAY(orders.orderId)

-- ON NULL
JSON_ARRAY(CAST(NULL AS STRING) NULL ON NULL) -- '[null]'
JSON_ARRAY(CAST(NULL AS STRING) ABSENT ON NULL) -- '[]'

-- '[[1]]'
JSON_ARRAY(JSON_ARRAY(1))

implicit constructor with parenthesis

(value1 [, value2]*)

explicit ROW constructor with

ROW(value1 [, value2]*)

通过将 key-value 聚合到单个 JSON 对象中，构建 JSON 对象字符串。

键表达式必须返回不为空的字符串。值表达式可以是任意的，包括其他 JSON 函数。 如果值为 NULL，则 ON NULL 行为定义了要执行的操作。如果省略，默认情况下假定为 NULL ON NULL。

请注意，键必须是唯一的。如果一个键出现多次，将抛出一个错误。

目前在 OVER windows 中不支持此函数。

-- '{"Apple":2,"Banana":17,"Orange":0}'
SELECT
  JSON_OBJECTAGG(KEY product VALUE cnt)
FROM orders

通过将字段聚合到数组中构建 JSON 对象字符串。

项目表达式可以是任意的，包括其他 JSON 函数。如果值为 NULL，则 ON NULL 行为定义了要执行的操作。如果省略，默认情况下假定为 ABSENT ON NULL。

此函数目前不支持 OVER windows、未绑定的 session windows 或 hop windows。

-- '["Apple","Banana","Orange"]'
SELECT
  JSON_ARRAYAGG(product)
FROM orders