postgresql【JSONB用法】

//userName type code 是我拿到数据结构出来的 可以写固定值来测试；code字段为上面设置的唯一约束。如果code值没有变就是修改，否则就是新增
INSERT INTO 表名(username, type, code) 
VALUES('${userName}', '${type}', '${code}') 
ON CONFLICT     9.6语法支持
(code) DO UPDATE 
SET username = '${userName}',
type = '${type}',
code = '${code}'

















-- 在PostgreSQL中，可以使用以下语法来操作JSON类型的数据： 
 
-- 1. 读取JSON字段的值：
SELECT my_json -> 'a' FROM 表名;
-- 这将返回JSON字段中指定键的值。 
 
-- 2. 读取JSON字段的某个属性值：
SELECT my_json -> 'key' -> 'subkey' FROM your_table;
-- 这将返回JSON字段中指定键和子键的值。 
 
-- 3. 更新JSON字段的值：

UPDATE your_table SET test_json = '{"b":1111}'   WHERE id=2;

UPDATE tenant_data_recordSET ext = jsonb_set (  ext,  '{update_category}',  '2')


-- 这将在原有JSON字段的基础上添加或更新指定键的值。 
 
-- 4. 添加新的键值对到JSON字段：
UPDATE your_table SET my_json = '{"b": 1, "a": 3}'  where your_table.id=1;
-- 这将在原有JSON字段的基础上添加新的键值对。 
 
-- 5. 删除JSON字段的键值对：
UPDATE your_table SET my_json = my_json - 'a'  where your_table.id=1;
-- 这将删除JSON字段中指定键的键值对。 



-- 以下是使用PostgreSQL操作JSON类型数据的一些示例： 
--  
-- 1. 读取JSON字段的值：
-- sql
-- SELECT json_column->'key' FROM table_name;
-- 这将返回JSON字段中指定键的值。 
--  
-- 2. 读取JSON字段的某个属性值：
-- sql
-- SELECT json_column->'key'->'subkey' FROM table_name;
-- 这将返回JSON字段中指定键和子键的值。 
--  
-- 3. 更新JSON字段的值：
-- sql
-- UPDATE table_name SET json_column = json_column || '{"key": "new_value"}' WHERE condition;
-- 这将在原有JSON字段的基础上添加或更新指定键的值。 
--  
-- 4. 添加新的键值对到JSON字段：
-- sql
-- UPDATE table_name SET json_column = json_column || '{"new_key": "new_value"}' WHERE condition;
-- 这将在原有JSON字段的基础上添加新的键值对。 
--  
-- 5. 删除JSON字段的键值对：
-- sql
-- UPDATE table_name SET json_column = json_column - 'key' WHERE condition;
-- 这将删除JSON字段中指定键的键值对。 
--  
-- 请注意，以上示例是基于使用PostgreSQL的JSON类型字段操作。如果使用的是JSONB类型字段，则可以使用更多高级的JSON操作函数和运算符。确保将示例中的表名、字段名和条件修改为实际使用的名称，并根据实际需求进行适当的调整。







INSERT INTO "file_manage_v2"."your_table" (
	"splan_id",
	"planname",
	"authstatus",
	"authuser_id",
	"authuser_nickname",
	"createuser_id",
	"createuser_nickname",
	"create_time",
	"update_time",
	"deleted",
	"skip_big",
	"skip_security",
	"my_json",
	"my_json" 
)
VALUES
	(
		2,
		'2',
		'2',
		2,
		'2',
		1,
		'2',
		'2023-10-16 11:25:30',
		'2023-10-16 11:25:30',
		0,
		0,
		0,
		'{"b": 3, "a": 4}',
	NULL 
	) ON CONFLICT (authuser_id,authuser_nickname,createuser_id)
	DO UPDATE
	SET authuser_id =2,authuser_nickname = '2',	createuser_id = 1;



INSERT INTO 表名(username, type, code) 
VALUES('${userName}', '${type}', '${code}') 
ON CONFLICT     9.6语法支持
(code) DO UPDATE 
SET username = '${userName}',
type = '${type}',code = '${code}'



成功案例：冲突键检测  需要有唯一索引才能生效
INSERT INTOyour_table ( "batch_code", "batch_cancel", "create_time", "update_time", "deleted") VALUES ('1', 1, '2023-10-16 16:49:01', NULL, 0)
	    (batch_code)	DO UPDATE
	SET batch_cancel =2;





--修改某个值
UPDATE your_table
SET test_json = jsonb_set(test_json, '{b}', '6'::jsonb)
WHERE id = 1;

-- 某个属性不存在 添加字段

UPDATE your_table
SET test_json = jsonb_set(test_json, '{sss}', '6'::jsonb)
WHERE id = 1;



ALTER TABLE your_table  ADD COLUMN test_json  jsonb ;
--  已经存在数据不可修改字段类型
ALTER TABLE your_table ALTER COLUMN my_json TYPE jsonb;
-- 固有字段 改变字段类型
ALTER TABLE your_table ALTER COLUMN my_json TYPE jsonb  USING (my_json::jsonb)

ALTER TABLE your_table ALTER COLUMN my_json TYPE jsonb  USING (my_json::jsonb);
ALTER TABLE your_table ALTER COLUMN my_json TYPE jsonb  USING (my_json::jsonb);

ALTER TABLE file_manage_v2.t_fm_auth_plan ALTER COLUMN audit_objective_json TYPE jsonb  USING (audit_objective_json::jsonb);


清除表的数据
   truncate your_table; 


-- 为空取新值，不为空给个默认值
UPDATE your_table
SET my_json = jsonb_set(
    my_json,
    '{b}',
    COALESCE(my_json->'b', '6'::jsonb, '0'::jsonb)
)
WHERE id=1;
WHERE <condition>;







在 PostgreSQL 中，可以使用内置的 JSONB 函数和操作符来对 JSONB 类型的字段进行加减运算。以下是一些常用的方法示例： 
 
1. 对 JSONB 字段的属性进行加法运算：
sql
------------------失效
UPDATE your_table
SET your_column = your_column || jsonb_build_object('your_property', (your_column->>'your_property')::numeric + 1)
WHERE <condition>;
在上面的示例中， your_table  是你的表名， your_column  是包含 JSONB 数据的列名， your_property  是要进行加法运算的属性名。通过使用  jsonb_build_object  函数和操作符  || ，将属性的值加 1，并将更新后的 JSONB 值存回原来的字段中。 
 
2. 对 JSONB 字段的属性进行减法运算：
sql
------------------失效
UPDATE your_table
SET your_column = your_column || jsonb_build_object('your_property', (your_column->>'your_property')::numeric - 1)
WHERE <condition>;
----------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 对JSONB的属性进行加减操作

postgresql 对JSONB属性为空的给个默认值，不为空的进行累加

-- 直接相加
UPDATE your_table
   SET my_json = jsonb_set(my_json, '{b}', to_jsonb((my_json ->>'b') :: numeric + 5))
 where id = 1;

--为空就给个0，然后相加，不为空直接相加
UPDATE your_table
   SET my_json = jsonb_set(my_json, '{b}',to_jsonb( COALESCE((my_json ->> 'b') :: numeric, 0) + 5))
 WHERE id = 1;

在上面的示例中， your_table  是你的表名， your_column  是包含 JSONB 数据的列名， your_property  是要进行减法运算的属性名。通过使用  jsonb_build_object  函数和操作符  || ，将属性的值减 1，并将更新后的 JSONB 值存回原来的字段中。 
 
请注意，以上示例中的  <condition>  部分需要根据你的具体需求进行修改，以指定更新的条件。

-- 直接相加
UPDATE your_table
   SET my_json = jsonb_set(my_json, '{b}', to_jsonb((my_json ->>'b') :: numeric + 5))
 where id = 1;





--为空就给个0，然后相加，不为空直接相加
UPDATE your_table
   SET my_json = jsonb_set(my_json, '{b}',to_jsonb( COALESCE((my_json ->> 'b') :: numeric, 0) + 5))
 WHERE id = 1;
 
 
 UPDATE your_table
   SET my_json = jsonb_set(my_json, '{b}',to_jsonb( COALESCE((my_json ->> 'b') :: numeric, 0) - 5))
 WHERE id = 1;

 UPDATE your_table
   SET my_json = jsonb_set(my_json, '{waitAudit}',to_jsonb( COALESCE((my_json ->> 'waitAudit') :: numeric, 0) -1))
 WHERE id = 2;




同时变更多个字段 (仅限于直接改变属性值，不适合做运算)
UPDATE your_table
   SET my_json=my_json::jsonb|| '{"ss": 1, "b": 1, "拟控制": 1, "a": 1}'::jsonb
 WHERE id =1;



同时变更多个字段并进行运算
 UPDATE your_table
SET my_json = jsonb_set(
  jsonb_set(
    my_json,
    '{ss}',
    to_jsonb(COALESCE((my_json->>'ss')::int, 0) + 5)
  ),
  '{b}',
  to_jsonb(COALESCE((my_json->>'b')::int, 0) - 1)
)
WHERE id=1;




在 PostgreSQL 中，单引号和双引号具有不同的含义和用途。基本区别如下：

单引号：表示字符串值。在 SQL 查询中，字符串通常使用单引号括起来。例如，’Hello World’。
双引号：表示标识符或对象名称。标识符可以是表名、列名、数据库名等。双引号使得标识符区分大小写，并且可以包含特殊字符或保留字。例如，”Table_Name”。




















postgresql处理jsonb类型字段多个属性进行算术运算
 List<JSONObject> jsonObjectList = new ArrayList<>();
        jsonObjectList.add(JSON.parseObject("{'key':'ss','value':1}"));
//        jsonObjectList.add(JSON.parseObject("{'key':'b','value':2}"));
//        jsonObjectList.add(JSON.parseObject("{'key':'a','value':3}"));
        StringBuilder  finalList = new StringBuilder();
        int size = jsonObjectList.size();
        if (size == 1) {
            for (JSONObject jsonObject : jsonObjectList) {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.append("my_json = jsonb_set(my_json");
                String key = jsonObject.getString("key");
                int value = jsonObject.getIntValue("value");
                stringBuilder.append(", '{" + key + "}',to_jsonb( COALESCE((my_json ->> '" + key + "') :: numeric, 0) + " + value + ")");
                stringBuilder.append(")");
                finalList.append(stringBuilder);
            }
        }else if(size>1){
             for(int i = 0; i < jsonObjectList.size(); i++) {
                StringBuilder stringBuilder = new StringBuilder();
                if(i==jsonObjectList.size()-1){
                    stringBuilder.append("my_json = jsonb_set(");
                }else if(i==0){
                    stringBuilder.append(" jsonb_set(my_json");
                }else{
                    stringBuilder.append(" jsonb_set(");
                }
                String key = jsonObjectList.get(i).getString("key");
                int value = jsonObjectList.get(i).getIntValue("value");
                stringBuilder.append(finalList);
                stringBuilder.append(", '{" + key + "}',to_jsonb( COALESCE((my_json ->> '" + key + "') :: numeric, 0) + " + value + ")");
                stringBuilder.append(")");
                finalList.delete(0,finalList.length());
                finalList.append(stringBuilder);
            }
        }
        System.out.println(">>>>>>>>>>>>>>>>>:"+finalList);
然后把拼接的sql放入更新语句
 userDao.update(null, new UpdateWrapper<User>().lambda()
                    .eq(User::getPlanId, planId).eq(User::getCreateuserId, userId).setSql(finalList.toString()));
https://blog.csdn.net/qq_35987023/article/details/132173013
https://blog.csdn.net/weixin_42800689/article/details/91413254