UDW中Json类型

Json相关操作

操作符	参数类型	作用	例子	执行结果
->	int	获取JSON数组元素，索引以0为开始	select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2;	{"c":"baz"}
->	text	通过键来获取 JSON 对象的域（field）	select '{"a": {"b":"foo"}}'::json->'a';	{"b":"foo"}
->>	int	获取 JSON 数组元素，然后以 text 形式返回它	select '[1,2,3]'::json->>2;	3
->>	text	获取 JSON 对象的域，然后以 text 形式返回它	select '{"a":1,"b":2}'::json->>'b';	2
#>	text[]	获取指定路径上的 JSON 对象	select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}';	{"c": "foo"}
#>>	text[]	获取指定路径上的 JSON 对象，并以 text 形式返回它	select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';	3

Json操作举例

创建json类型的表格，插入数据

sql create table test_json(id int , name json) with (APPENDONLY=true,ORIENTATION=column,compresslevel=5) DISTRIBUTED BY (id); INSERT INTO test_json VALUES (1,'{ "id": 1, "sub": { "subid": 10,"subsub": {"subsubid": 100}}}'), (2,'{ "id": 20, "sub": { "subid": 200,"subsub": {"subsubid": 2000}}}'), (1,'{ "id": 1, "sub": { "subid": "test","subsub": {"subsubid": 100}}}'), (3,'{ "id": 1,"sub":"test","name":"me","ip":"10.10.10.10" }');

json操作类型操作举例：

sql select * from test_json where name->>'id'=1; id | name ----+------------------------------------------------------------------- 1 | { "id": 1, "sub": { "subid": 10,"subsub": {"subsubid": 100}}} 1 | { "id": 1, "sub": { "subid": "test","subsub": {"subsubid": 100}}} 3 | { "id": 1,"sub":"test","name":"me","ip":"10.10.10.10" } select * from test_json where name->'sub'->>'subid'=10; id | name ----+--------------------------------------------------------------- 1 | { "id": 1, "sub": { "subid": 10,"subsub": {"subsubid": 100}}} select * from test_json where name->>'name'='me'; id | name ----+--------------------------------------------------------- 3 | { "id": 1,"sub":"test","name":"me","ip":"10.10.10.10" }

Json相关函数

Json创建函数

json to_json(anyelement) array_to_json(anyarray [, pretty_bool]) row_to_json(record [,pretty_bool]) json_build_array(VARIADIC "any") json_build_object(VARIADIC "any") json_object(text[]) json_object(keys text[], values text[])

Json处理函数有

json json_array_length(json) json_extract_path(from_json json, VARIADIC path_elems text[]) json_extract_path_text(from_json json, VARIADICpath_elems text[]) json_object_keys(json) json_populate_record( base anyelement, from_json json) json_typeof(json) json_to_record(json) json_to_recordset(json)

json函数的详细操作请参考文档下面的部分。

## Json创建函数

json to_json(anyelement)

以 JSON 格式返回输入的值。数组和复合数据会被（递归地）转换为数组和对象；如果有转换函数可以将输入的数据转换为 json 的话，那么使用转换函数；或者产生一个 JSON 标量（scalar）值。数字、布尔值和空值（null）之外的其他标量会被表示为文本格式，并通过正确的引用和转义来保证它是一个合法的 JSON 字符串。如下所示：