文档首页/ 数据湖探索 DLI/ HetuEngine SQL语法参考/ HetuEngine SQL语法/ SQL函数和操作符/ Json函数和运算符
更新时间：2024-12-13 GMT+08:00
查看PDF
分享

Json函数和运算符

  • Cast to JSON 
    SELECT CAST(9223372036854775807 AS JSON); -- JSON '9223372036854775807'
  • Cast from JSON 
    SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]

JSON函数

NULL到JSON的转换并不能简单地实现。从独立的NULL进行转换将产生一个SQLNULL，而不是JSON 'null'。不过，在从包含NULL的数组或Map进行转换时，生成的JSON将包含NULL。

在从ROW转换为JSON时，结果是一个JSON数组，而不是一个JSON对象。这是因为对于SQL中的行，位置比名称更重要。

支持从BOOLEAN、TINYINT、SMALLINT、INTEGER、BIGINT、REAL、DOUBLE或VARCHAR进行转换。当数组的元素类型为支持的类型之一、Map的键类型是VARCHAR且Map的值类型是支持的类型之一或行的每个字段类型是支持的类型之一时支持从ARRAY、MAP或ROW进行转换。下面通过示例展示了转换的行为：

SELECT CAST(NULL AS JSON);-- NULL
SELECT CAST(1 AS JSON);-- JSON '1'
SELECT CAST(9223372036854775807 AS JSON);-- JSON '9223372036854775807'
SELECT CAST('abc' AS JSON);-- JSON '"abc"'
SELECT CAST(true AS JSON);-- JSON 'true'
SELECT CAST(1.234 AS JSON);-- JSON '1.234'
SELECT CAST(ARRAY[1, 23, 456] AS JSON);-- JSON '[1,23,456]'
SELECT CAST(ARRAY[1, NULL, 456] AS JSON);-- JSON '[1,null,456]'
SELECT CAST(ARRAY[ARRAY[1, 23], ARRAY[456]] AS JSON);-- JSON '[[1,23],[456]]'
SELECT CAST(MAP(ARRAY['k1', 'k2', 'k3'], ARRAY[1, 23, 456]) AS JSON);-- JSON '{＂k1＂:1,＂k2＂:23,＂k3＂:456}'
SELECT CAST(CAST(ROW(123, 'abc', true) AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)) AS JSON);-- JSON '[123,"abc",true]'

JSON 转其它类型

SELECT CAST(JSON 'null' AS VARCHAR);-- NULL  
SELECT CAST(JSON '1' AS INTEGER);-- 1  
SELECT CAST(JSON '9223372036854775807' AS BIGINT);-- 9223372036854775807  
SELECT CAST(JSON '"abc"' AS VARCHAR);-- abc  
SELECT CAST(JSON 'true' AS BOOLEAN);-- true  
SELECT CAST(JSON '1.234' AS DOUBLE);-- 1.234  
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER));-- [1, 23, 456]  
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER));-- [1, NULL, 456]  
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER)));-- [[1, 23], [456]]  
SELECT CAST(JSON '{"k1":1, "k2":23, "k3":456}' AS MAP(VARCHAR, INTEGER));-- {k1=1, k2=23, k3=456}  
SELECT CAST(JSON '{"v1":123, "v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN));-- {v1=123, v2=abc, v3=true}  
SELECT CAST(JSON '[123, "abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN));-- {value1=123, value2=abc, value3=true}  
SELECT CAST(JSON'[[1, 23], 456]'AS ARRAY(JSON));-- [JSON '[1,23]', JSON '456']  
SELECT CAST(JSON'{"k1": [1, 23], "k2": 456}'AS MAP(VARCHAR,JSON));-- {k1 = JSON '[1,23]', k2 = JSON '456'}  
SELECT CAST(JSON'[null]'AS ARRAY(JSON));-- [JSON 'null']

在从JSON转换为ROW时，支持JSON数组和JSON对象。

JSON数组可以具有混合元素类型，JSON Map可以有混合值类型。这使得在某些情况下无法将其转换为SQL数组和Map。为了解决该问题，HetuEngine支持对数组和Map进行部分转换：

SELECT CAST(JSON'[[1, 23], 456]'AS ARRAY(JSON));-- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON'{"k1": [1, 23], "k2": 456}'AS MAP(VARCHAR,JSON));-- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON'[null]'AS ARRAY(JSON));-- [JSON 'null']
  • is_json_scalar(json) → boolean

    判断json是否为标量（即JSON数字、JSON字符串、true、false或null）：

    select is_json_scalar(json'[1,22]'); -- false
  • json_array_contains(json, value) → boolean

    判断json中是否包含某value

    select json_array_contains(json '[1,23,44]',23); -- true
  • json_array_get(json_array, index) → json

    该函数的语义已被破坏。如果提取的元素是字符串，它将被转换为未正确使用引号括起来的无效JSON值（值不会被括在引号中，任何内部引号不会被转义）。建议不要使用该函数。无法在不影响现有用法的情况下修正该函数，可能会在将来的版本中删除该函数。

    返回指定索引位置的json元素，索引从0开始

    SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'

    索引页支持负数，表示从最后开始，-1表示最后一个元素，索引超过实际长度会返回null

    SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]'

    如果指定索引位置的json元素不存在，将返回NULL值

    SELECT json_array_get('[]', 0);                -- NULL
SELECT json_array_get('["a", "b", "c"]', 10);  -- NULL
SELECT json_array_get('["c", "b", "a"]', -10); -- NULL
  • json_array_length(json) → bigint

    返回json的长度

    SELECT json_array_length(json '[1,2,3,4]'); -- 4
SELECT json_array_length('[1, 2, 3]'); -- 3
  • get_json_object(string json,string json_path)；

    按照json_path格式抓取json中的信息

    SELECT get_json_object('{"id": 1, "value":"xxx"}', '$.value');  -- "xxx"
  • json_extract(json, json_path) → json

    按照json_path格式抓取json中的信息

    SELECT json_extract(json '{"id": 1, "value":"xxx"}', '$.value');-- JSON "xxx"
  • json_extract_scalar(json, json_path) → varchar

    和json_extract功能相同，返回值是varchar

    SELECT json_extract_scalar(json '{"id": 1, "value": "xxx"}', '$.value'); -- xxx
  • json_format(json) → varchar

    把json值转为序列化的json文本，这是json_parse的反函数：

    SELECT JSON_format(json '{"id": 1, "value":"xxx"}'); -- {"id":1, "value":"xxx"}

    注意：

    json_format和CAST(json AS VARCHAR)具有完全不同的语义。

    json_format将输入JSON值序列化为遵守7159标准的JSON文本。JSON值可以是JSON对象、JSON数组、JSON字符串、JSON数字、true、false或null：

    SELECT json_format(JSON '{"a": 1, "b": 2}'); -- '{"a":1,"b":2}' 
SELECT json_format(JSON '[1, 2, 3]'); -- '[1,2,3]' 
SELECT json_format(JSON '"abc"'); -- '"abc"' 
SELECT json_format(JSON '42'); -- '42' 
SELECT json_format(JSON 'true'); -- 'true' 
SELECT json_format(JSON 'null'); -- 'null'

    CAST(json AS VARCHAR)将JSON值转换为对应的SQL VARCHAR值。对于JSON字符串、JSON数字、true、false或null，转换行为与对应的SQL类型相同。JSON对象和JSON数组无法转换为VARCHAR：

    SELECT CAST(JSON '{"a": 1, "b": 2}' AS VARCHAR); -- ERROR!
SELECT CAST(JSON '[1, 2, 3]' AS VARCHAR);        -- ERROR!
SELECT CAST(JSON '"abc"' AS VARCHAR);            -- 'abc' (the double quote is gone)
SELECT CAST(JSON '42' AS VARCHAR);               -- '42'
SELECT CAST(JSON 'true' AS VARCHAR);             -- 'true'
SELECT CAST(JSON 'null' AS VARCHAR);             -- NULL
  • json_parse(string) → json

    和json_format(json)功能相反，将json格式的字符串转换为json

    Json_parse和json_extract通常结合使用，用于解析数据表中的json字符串

    select JSON_parse('{"id": 1, "value":"xxx"}'); -- json {"id":1, "value":"xxx"}
  • json_size(json, json_path) → bigint

    和json_extract类似，但是返回的是json里的对象个数

    SELECT json_size('{ "x": {"a": 1, "b": 2} }', '$.x'); => 2
SELECT json_size('{ "x": [1, 2, 3] }', '$.x'); =>3
SELECT json_size('{ "x": {"a": 1, "b": 2} }', '$.x.a'); => 0
父主题： SQL函数和操作符

相关文档