系统（内置）函数 #

返回给定时间列属性 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.

-- "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)

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 object string by aggregating key-value expressions into a single JSON object.

The key expression must return a non-nullable character string. Value expressions can be arbitrary, including other JSON functions. If a value is NULL, the ON NULL behavior defines what to do. If omitted, NULL ON NULL is assumed by default.

Note that keys must be unique. If a key occurs multiple times, an error will be thrown.

This function is currently not supported in OVER windows.

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

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))

Builds a JSON object string by aggregating items into an array.

Item expressions can be arbitrary, including other JSON functions. If a value is NULL, the ON NULL behavior defines what to do. If omitted, ABSENT ON NULL is assumed by default.

This function is currently not supported in OVER windows, unbounded session windows, or hop windows.

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