|
登录 类似于 mysql -uroot -p
psql -U postgre postgre：用户名
查看数据库 类似于show databases
\l 
选中数据库 类似于use hsrg
查看数据库表 类似于 show tables
HAVING
SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1; 必须再group by后面，相当于分组完成之后加判断条件
当jsonb字段为空时，直接使用json_set无法更新成功
首先发现，当字段为null时，怎么修改都不好使，提示成功，查询却没有。但是如果字段给输入{}时却可以更新成功，那就先给他处理下。
#可以更新、插入jsonb的单个值
UPDATE hs_report_task SET extend_param = coalesce(extend_param::jsonb,'{}'::jsonb) || '{"array":[{"bb":"cc"}]}'::jsonb where id = '017544b27e9a427f87cfd3a757b01ed4';
#返回值如果为空，则直接返回{}，否则返回该字段值
select coalesce(extend_param::jsonb,'{}'::jsonb) from hs_report_task
#返回值，如果为空返回{}，否则返回||后面的值
select coalesce(extend_param::jsonb,'{}'::jsonb) || '{"array":[{"bb":"cc"}]}'::jsonb from hs_report_task
#直接覆盖更新，不是我要的，这种直接再见
UPDATE hs_report_task SET extend_param = '{"dd":"kk"}'::jsonb where id = '062ecbf98ed14aa29e5875ecce36cdfe';
嵌套的JSON查询
INSERT INTO test (uid, tooldata)
VALUES (3, '{
"a": [{
"b": "ccccc",
"d": [{
"e": "eeeee",
"f": {
"g": "ggggggggggg",
"h": "HHHHHHHHHHHHHH"
}
}, {
"e": "fffffffff",
"f": {
"g": "rrrrrrrrrr",
"h": "66666666666"
}
}]
}, {
"b": "ccccc",
"d": [{
"e": "eeeee",
"f": {
"g": "TTTTTTTTT",
"h": "UUUUUUU"
}
}, {
"e": "QQQQQQQQ",
"f": {
"g": "WWWWWWWWW",
"h": "4444444444"
}
}]
}]
}');
#获取items 中的 a 数组对象： 得到结果为一行的jsonb 的数组格式
select tooldata ->> 'a' from test;
#获取a数组对象中的d数组对象：得到结果为d的jsonb的数组格式，由于第二步中a中的数组分为两行 所以会有两行的d的jsonb的数组格式
select jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d' from test;
#第三步中的结果为数组格式，则如同第二步操作一样，转换为jsonb 格式后拆分数组转化为多行对象 得到的结果为四行的d中的对象
select jsonb_array_elements((jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb) from test;
#获取d对象中的f对象 得到的结果为四行的f对象
select jsonb_array_elements((jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb)->>'f' from test;
#在获取了单层的对象后就可以结合jsonb的查询操作进行对数据的筛选 先通过子查询 将 items字段转化为多行的f对象
select (jsonb_array_elements((jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb)->>'f')::jsonb->>'h' from test;
#路径替换值
select jsonb_set(tooldata,'{a,0,b}','"test"') from test where uid=3
#路径添加值，添加一个JSON数据
select jsonb_set(tooldata,'{a,0,d,3}','{"test":"test1"}') from test where uid=3
依次输出      
jsonb数组更新操作
#更新字段
UPDATE hs_report_task SET extend_param = coalesce(extend_param::jsonb,'{}'::jsonb) || '{"test":[{"uid": 1, "name": "标签名1", "add_time": "2021-05-29 17:00:00"}, {"uid": 2, "name": "标签名2", "add_time": "2021-05-29 17:00:00"}]}'::jsonb where id = '062ecbf98ed14aa29e5875ecce36cdfe';
#查询字段结果
{"dd": "kk", "hr": "12", "test": [{"uid": 1, "name": "标签名1", "add_time": "2021-05-29 17:00:00"}, {"uid": 2, "name": "标签名2", "add_time": "2021-05-29 17:00:00"}]}
#更新，按路径更新，这种方法不可取，毕竟需要知道去确切的路径
update test set tooldata = jsonb_set(tooldata,'{a,0,d,3}','{"test":"test1"}') from test where uid=3
#刪除路徑上的一個值
update test set tooldata = tooldata #- '{a,0,d,0,f,g}' where uid=3
#删除前
{
"a": [{
"b": "ccccc",
"d": [{
"e": "eeeee",
"f": {
"g": "ggggggggggg", #删除的值
"h": "HHHHHHHHHHHHHH"
}
},
{
"e": "fffffffff",
"f": {
"g": "rrrrrrrrrr",
"h": "66666666666"
}
},
{
"test": "test4"
},
{
"test": "test3"
}]
},
{
"b": "ccccc",
"d": [{
"e": "eeeee",
"f": {
"g": "TTTTTTTTT",
"h": "UUUUUUU"
}
},
{
"e": "QQQQQQQQ",
"f": {
"g": "WWWWWWWWW",
"h": "4444444444"
}
}]
}]
}
#删除后
{
"a": [{
"b": "ccccc",
"d": [{
"e": "eeeee",
"f": {
"h": "HHHHHHHHHHHHHH"
}
},
{
"e": "fffffffff",
"f": {
"g": "rrrrrrrrrr",
"h": "66666666666"
}
},
{
"test": "test4"
},
{
"test": "test3"
}]
},
{
"b": "ccccc",
"d": [{
"e": "eeeee",
"f": {
"g": "TTTTTTTTT",
"h": "UUUUUUU"
}
},
{
"e": "QQQQQQQQ",
"f": {
"g": "WWWWWWWWW",
"h": "4444444444"
}
}]
}]
}
#以下为一个测试代码
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb from test where uid = 3;
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb-0 from test where uid = 3;
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb-1 from test where uid = 3;
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb-2 from test where uid = 3;
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb-3 from test where uid = 3;
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb-4 from test where uid = 3;
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb from test where uid = 3;
SELECT (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb #- '{0,f,g}' from test where uid = 3;
select (jsonb_array_elements((tooldata ->> 'a')::jsonb)->>'d')::jsonb from test where uid = 3;
通过replace实现的jsonb更新
create table purchase_order
(
id serial not null primary key,
tag jsonb
)
INSERT INTO purchase_order (id, tag) VALUES (4787, '[{"uid": 1, "name": "标签名1", "add_time": "2021-05-29 17:00:00"}, {"uid": 2, "name": "标签名2", "add_time": "2021-05-29 17:00:00"}]');
select *from purchase_order
-- 嵌套子查询
select * from (
select jsonb_array_elements(tag) as tt from purchase_order where id = 4787) a
where tt -> 'uid' = '1';
-- 或者这种方式
SELECT
id,r
FROM
purchase_order s, jsonb_array_elements(s.tag) r
WHERE
s.id = 4787 and r->>'uid' = '2' ;
UPDATE purchase_order SET tag = tag || '[{
"uid": 3,
"name": "标签名3",
"add_time": "2021-05-29 17:00:00"
}]' where id = 4787;
SELECT tag FROM purchase_order
WHERE id = 4787 and tag @> '[{"uid": 3}]';
-- 更新操作，这种也可行，但是不是我想要的，仅仅记录。
UPDATE purchase_order AS g
SET tag = REPLACE(tag::text, '"name": "标签名1"','"name": "标签new"')::json
WHERE g.tag IN ( SELECT g.tag
FROM purchase_order AS g
CROSS JOIN jsonb_array_elements(g.tag) AS j
WHERE id = 4787 and j ->>'uid' = '1' )
JSONB的遍历显示
CREATE TABLE person(id serial, info jsonb);
INSERT INTO person (info) VALUES ('{"num":"1","name":"张三","score":"90"}'::jsonb);
SELECT t.* FROM person, jsonb_to_record(info) AS t(num text, name text, score text);
# 更新的操作，目前还没看懂
UPDATE person t1 SET info = jsonb_set(info, array[(SELECT ORDINALITY::INT - 1 FROM person t2, jsonb_array_elements(info) WITH
ORDINALITY WHERE t1.id = t2.id AND value->>'num' = '1')::text, 'score'::text], '"92"') WHERE id = '1'
 
|