postgresql【JSONB用法】

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346

//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