Json基本操作
获取JSON数组元素，索引以0为开始
select '[{"a":"foo"},{"b":"bar"},{"c":"foo"}]'::json->2;
通过键来获取 JSON 对象的域（field）
select '{"a":{"b":"foo"}}'::json->'a';
获取 JSON 数组元素，然后以 text 形式返回它
select '[1,2,3]'::json->>2;
获取 JSON 对象的域，然后以 text 形式返回它
select '{"a":1,"b":2}'::json->>'b';
获取指定路径上的 JSON 对象
select '{"a":{"b":{"c":"foo"}}}'::json#>'{a,b}';
获取指定路径上的 JSON 对象，并以 text 形式返回它
select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';
创建Json表：
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"}');
SELECT * FROM test_json WHERE name->>'id'=1;
SELECT * FROM test_json WHERE name->'sub'->>'subid'=10;
SELECT * FROM test_json WHERE name->>'name'='me';
创建函数
以 JSON 格式返回输入的值。
select to_json('a said "Hi."'::text);
以 JSON 数组格式返回输入的数组。如果 pretty_bool的值为 true ， 那么则在维度-1元素之间添加换行符。 如下所示：
select array_to_json(array_agg(q), false) from (select x as b,x*2 as c from generate_series(1,3) x)q;
以 JSON 对象格式返回行。
select row_to_json(row(1,'foo'));
建立一个可能不同类型的JSON数组，由可变参数列表组成
select json_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x":3,"y":[1,2,3]}');
输入的文本数组构建一个 JSON 对象。
select json_object('{{a,1},{b,2},{3,null},{"d e f","a b c"}}');
select json_object('{a,b,c,"d e f"}','{1,2,3,"a b c"}');
返回最外层的 JSON 数组的元素数量。
select json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
练习
- 创建一张普通数据表t1并且导入数据;
- 创建一张json数据表t1_json；
- 将普通表t1中的数据转换为json格式的数据并且存储在t1_json 中。
create table t1(c1 int,c2 int);
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);
select * from t1;
create table t1_json(c1 json);
insert into t1_json select row_to_json(row(q)) from (select * from t1) q;
select * from t1_json;
- 创建一张json数据表t2_json并且导入数据；
- 创建一张普通数据表t2;
- 将t2_json表中数据进行转换并且存储在普通表中t2中。
create table t2_json(c1 json);
insert into t2_json values
('{"a":"gg","b":"jj"}'),
('{"a":"ggg","b":"jjj"}'),
('{"a":"gggg","b":"jjjj"}');
create table t2(a text,b text);
insert into t2 select cast(c1->'a' as text),cast(c1->'b' as text) from t2_json;
