ORACLE PLSQL – 根据条件将多行的JSON数据合并为单个JSON

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

ORACLE PLSQL - Merge JSON Data of multiple rows into a single JSON based on conditions

问题

我有一张表格,其中有如下列:

ID         JSON_COL                                                   GROUP
1          { "numbers" : [ 1 , 2 ], alphabets : [ "a" , "b" ] }       1
2          { "numbers" : [ 3 , 4 ], alphabets : [ "c" , "d" ] }       1
3          { "numbers" : [ 5 , 6 ], alphabets : [ "e" , "f" ] }       2
4          { "numbers" : [ 7 , 8 ], alphabets : [ "g" , "h" ] }       2
5          { "numbers" : [ 9 , 10 ], alphabets : [ "i" , "j" ] }      2

我需要根据组合并这些JSON为一个单一的JSON。例如:当我根据组 = 1 进行筛选时,我希望以下结果

{ "numbers" : [ 1 , 2, 3, 4 ], alphabets : [ "a" , "b" , "c" , "d" ] }

当我根据组 = 2 进行筛选时,我期望以下结果

{ "numbers" : [ 5 , 6 , 7 , 8 , 9 , 10 ], alphabets : [ "e" , "f" , "g" , "h" , "i" , "j" ] }

请注意,我是PLSQL的初学者,难以解决这个问题。我尝试了一些方法,比如

JSON_TRANSFORM,但它只有帮助合并两个不同的列。但我想合并相同列的多行。

英文:

I have a table which has columns like below

ID         JSON_COL                                                   GROUP
1          { "numbers" : [ 1 , 2 ], alphabets : [ "a" , "b" ] }       1
2          { "numbers" : [ 3 , 4 ], alphabets : [ "c" , "d" ] }       1
3          { "numbers" : [ 5 , 6 ], alphabets : [ "e" , "f" ] }       2
4          { "numbers" : [ 7 , 8 ], alphabets : [ "g" , "h" ] }       2
5          { "numbers" : [ 9 , 10 ], alphabets : [ "i" , "j" ] }      2

I need to merge the JSONs into one single one based on the group
example : when I filter based on group = 1, I want the below result

{ "numbers" : [ 1 , 2, 3, 4 ], alphabets : [ "a" , "b" , "c" , "d" ] }

and when I filter based on group = 2 , then I expect the result like below

{ "numbers" : [ 5 , 6 , 7 , 8 , 9 , 10 ], alphabets : [ "e" , "f" , "g" , "h" , "i" , "j" ] }

Kindly note that am a beginner in PLSQL and finding it difficult to solve this problem.
I tried few things like

JSON_TRANSFORM but it was helping to merge two different columns. But I am looking to merge the same column's multiple rows here.

答案1

得分: 3

你可以将所有内容聚合到一个单一的数组中,然后使用JSON查询并让JSON路径在每个数组元素内部以数组步骤路径前进:[*].path.to.data

对于你的示例数据:

with grp as (
   select
     json_arrayagg(json_col format json) as agg
   from sample s
   where grp = 1
)
select
  json_object(
    key 'numbers' value json_query(
      agg format json,
      '$[*].numbers[*]' returning clob
      with unconditional array wrapper
    ),
    key 'alphabets' value json_query(
      agg format json,
      '$[*].alphabets[*]' returning clob
      with unconditional array wrapper
    )
  ) as result
from grp
RESULT
{"numbers":[1,2,3,4],"alphabets":["a","b","c","d"]}

fiddle

英文:

You may aggregate everything into a single array, then use JSON query and let JSON path to traverse inside each array element with array step before path: [*].path.to.data.

For your sample data:

with grp as (
   select
     json_arrayagg(json_col format json) as agg
   from sample s
   where grp = 1
)
select
  json_object(
    key 'numbers' value json_query(
      agg format json,
      '$[*].numbers[*]' returning clob
      with unconditional array wrapper
    ),
    key 'alphabets' value json_query(
      agg format json,
      '$[*].alphabets[*]' returning clob
      with unconditional array wrapper
    )
  ) as result
from grp
RESULT
{"numbers":[1,2,3,4],"alphabets":["a","b","c","d"]}

fiddle

huangapple
  • 本文由 发表于 2023年8月4日 02:46:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/76830871.html
匿名

发表评论

匿名网友

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

确定