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

Question

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.

Answer 1

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