作者
digoal
日期
2019-06-11
标签
PostgreSQL , oracle , 兼容性 , pljson
背景
pljson是Oracle的一个外部包。提供了一些JSON操作的接口。
The goal of PL/JSON is to create a correct implementation of JSON to use in a PL/SQL environment. The Oracle object syntax has been chosen to ensure a straightforward and easy way to decode and encode JSON. PL/JSON is delivered AS IS and we cannot make any guarantee or be held responsible to any unwanted effects that may arise from using this software. However, we would like to stress that we have tested, and used this software, and believe that it is a safe product to use.
Features of PL/JSON include:
Basic JSON Path support
Optional "tweaks" to adjust PL/JSON's behavior
PostgreSQL JSON的类型支持远远早于Oracle，从功能、实现方面来讲，PG的JSON也确实更加强大。
PostgreSQL的JSON使用
1、类型
json
jsonb
2、操作符和函数
https://www.postgresql.org/docs/12/functions-json.html
3、扩展包
https://github.com/postgrespro/jsquery
例子
pljson提供的path操作接口例子
```
The JSON object:
{
"xyz" : {
"abc" : [1,2,3,[4,5,{"123":45}]]
}
}
Extract the abc list:
pljson_ext.get_json_list(obj, 'xyz.abc').print;
-- [1, 2, 3, [4 ,5 , {
-- "123" : 45
-- }]]
Extract the 123 number:
pljson_ext.get_number(obj, 'xyz.abc[4][3].123').print;
-- 45
As of version 0.8.4, square brackets can be used to extract JSON members like you would do in JavaScript:
pljson_ext.get_number(obj, '["xyz"]["abc"][4][3]["123"]').print;
-- 45
You can also use JSON Path to modify an existing JSON object:
pljson_ext.put(obj, 'xyz.abc', pljson('{"val":123}'));
-- {
-- "xyz" : {
-- "abc" : {
-- "val" : 123
-- }
-- }
-- }
Removing unwanted elements is also an option:
pljson_ext.remove(obj, 'xyz.abc');
-- {
-- "xyz" : {
-- }
-- }
```
在PG中，我们查看https://www.postgresql.org/docs/12/functions-json.html手册，可以看到对应的用法
1、PATH
jsonb_extract_path
postgres=# do language plpgsql $$
postgres$# declare
postgres$# obj jsonb := '{
postgres$# "xyz" : {
postgres$# "abc" : [1,2,3,[4,5,{"123":45}]]
postgres$# }
postgres$# }';
postgres$# begin
postgres$# raise notice '%', jsonb_extract_path(obj, 'xyz', 'abc');
postgres$# end;
postgres$# $$;
psql: NOTICE: [1, 2, 3, [4, 5, {"123": 45}]]
DO
2、value
数组ID 0开始。与pljson不一样。
postgres=# do language plpgsql $$
declare
obj jsonb := '{
"xyz" : {
"abc" : [1,2,3,[4,5,{"123":45}]]
}
}';
begin
raise notice '%', obj->'xyz'->'abc'->3->2->>'123';
end;
$$;
psql: NOTICE: 45
DO
3、upsert element
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
postgres=# do language plpgsql $$
declare
obj jsonb := '{
"xyz" : {
"abc" : [1,2,3,[4,5,{"123":45}]]
}
}';
begin
raise notice '%', jsonb_set(obj, '{xyz,abc}', '{"val":123}');
end;
$$;
psql: NOTICE: {"xyz": {"abc": {"val": 123}}}
DO
4、remove k-v
postgres=# do language plpgsql $$
declare
obj jsonb := '{
"xyz" : {
"abc" : [1,2,3,[4,5,{"123":45}]]
}
}';
begin
raise notice '%', obj #- '{xyz,abc}';
end;
$$;
psql: NOTICE: {"xyz": {}}
DO
更多强大的JSON操作功能参考手册和扩展包。
https://github.com/postgrespro/jsquery
https://www.postgresql.org/docs/12/functions-json.html
https://www.postgresql.org/docs/12/datatype-json.html
参考
https://pljson.github.io/pljson/
https://github.com/postgrespro/jsquery
https://www.postgresql.org/docs/12/functions-json.html
https://www.postgresql.org/docs/12/datatype-json.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
