如何在Oracle中通过将另一列的值添加到JSON值中来更新JSON值？

huangapple
go评论100阅读模式
英文:

How to update JSON value in Oracle by adding over there value from another column?

问题

以下是您提供的代码的翻译部分：

  1. DECLARE
  2.   v_my_json VARCHAR2(4000);
  3.   v_type NUMBER;
  4. BEGIN
  5.   FOR rec IN (
  6.     SELECT *
  7.     FROM database.table t
  8.     WHERE MY_JSON LIKE '{
  9.     "common": [{
  10.         "code": ["FIRST_CODE"]
  11.     }]'
  12.   )
  13.   LOOP
  15.     SELECT TYPE_ID
  16.     INTO v_type
  17.     FROM database.table
  18.     WHERE ID = rec.ID;
  20.     v_my_json := REPLACE(rec.MY_JSON,
  21.         '{
  22.     "common": [{
  23.         "code": ["FIRST_CODE"]
  24.     }]',
  25.         '{
  26.     "common": [{
  27.         "code": ["FIRST_CODE"], 
  28.         "type": ' || v_type || '}]');
  30.     UPDATE database.table
  31.     SET MY_JSON = v_my_json
  32.     WHERE ID = rec.ID;
  33.   END LOOP;
  34. END;
  35. /

注意：这段代码似乎用于在Oracle数据库中更新JSON数据。如果您有任何进一步的问题或需要帮助，请随时提出。

英文:

I would like to update values in JSON in my table in Oracle which look like (same values for about 80 rows):

  1. {
  2.     "common": [{
  3.         "code": ["FIRST_CODE"]
  4.     }]
  5. }

and make it looks like this:

  1. {
  2.     "common": [{
  3.         "code": ["FIRST_CODE"],
  4.         "type": 1
  5.     }]
  6. }

I want to get type from column TYPE_ID from the same table (it contains only 1 and 2 values).
Can you help me please - how can I do it correctly? Because now I've written the following script and it works but it did not change my table:

  1. DECLARE
  2.   v_my_json VARCHAR2(4000);
  3.   v_type NUMBER;
  4. BEGIN
  5.   FOR rec IN (
  6.     SELECT *
  7.     FROM database.table t
  8.     WHERE MY_JSON LIKE '{
  9.     "common": [{
  10.         "code": ["FIRST_CODE"]
  11.     }]
  12. }'
  13.   )
  14.   LOOP
  16.     SELECT TYPE_ID
  17.     INTO v_type
  18.     FROM database.table
  19.     WHERE ID = rec.ID;
  21.     v_my_json := REPLACE(rec.MY_JSON,
  22.         '{
  23.     "common": [{
  24.         "code": ["FIRST_CODE"]
  25.     }]
  26. }',
  27.         '{
  28.     "common": [{
  29.         "code": ["FIRST_CODE"], 
  30.         "type": ' || v_type || '}]');
  32.     UPDATE database.table
  33.     SET MY_JSON = v_my_json
  34.     WHERE ID = rec.ID;
  35.   END LOOP;
  36. END;
  37. /

答案1

得分: 0

请使用 JSON 函数而不是字符串函数来处理 JSON 数据，具体可以使用 JSON_TRANSFORM 函数。以下是您提供的代码示例：

  1. with sample(json_val, other_col) as (
  2.   select '{
  3.     "common": [{
  4.         "code": ["FIRST_CODE"]
  5.     }, {
  6.     "code": ["ANOTHER_ARRAY_ITEM"]
  7.   }]
  8. }', 1
  9.   from dual
  10. )
  11. select
  12.   json_val,
  13.   json_transform(
  14.     json_val,
  15.     insert '$.common[*].type' = other_col
  16.   ) as new_val
  17. from sample

此查询将在 json_val 的 JSON 数据中插入一个 type 字段，其值为 other_col，并返回新的 JSON 数据。表格中包含了原始数据和处理后的结果。

您还可以在 fiddle 上查看示例。

英文:

Don't use string functions to deal with JSON. Use JSON functions instead: JSON_TRANSFORM.

  1. with sample(json_val, other_col) as (
  2.   select '{
  3.     "common": [{
  4.         "code": ["FIRST_CODE"]
  5.     }, {
  6.     "code": ["ANOTHER_ARRAY_ITEM"]
  7.   }]
  8. }', 1
  9.   from dual
  10. )
  11. select
  12.   json_val,
  13.   json_transform(
  14.     json_val,
  15.     insert '$.common[*].type' = other_col
  16.   ) as new_val
  17. from sample
JSON_VAL NEW_VAL
{<br>    "common": [{<br>        "code": ["FIRST_CODE"]<br>    }, {<br>    "code": ["ANOTHER_ARRAY_ITEM"]<br>  }]<br>} {"common":[{"code":["FIRST_CODE"],"type":1},{"code":["ANOTHER_ARRAY_ITEM"],"type":1}]}

fiddle

huangapple
  • 本文由 发表于 2023年6月15日 03:33:20
  • 转载请务必保留本文链接：https://go.coder-hub.com/76476989.html
  • json
  • oracle
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定