By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE OR REPLACE VIEW v_test_rules AS
WITH test_rules (id,trx_id,customer_id,rules) AS
(
SELECT 1, 1, 'a', 3 FROM dual UNION ALL
SELECT 2, 1, 'a', 1 FROM dual UNION ALL
SELECT 6, 2, 'a', 5 FROM dual UNION ALL
SELECT 7, 2, 'a', 1 FROM dual UNION ALL
SELECT 10, 3, 'b', 1 FROM dual UNION ALL
SELECT 9, 3, 'b', 2 FROM dual UNION ALL
SELECT 8, 3, 'b', 3 FROM dual
), t AS
(
SELECT JSON_OBJECT(KEY 'trx_id' IS TO_CHAR(trx_id) FORMAT JSON,
KEY 'count_rules' IS TO_CHAR(COUNT(rules)) FORMAT JSON) AS jo,
customer_id
FROM test_rules
GROUP BY customer_id, trx_id
)
SELECT customer_id, JSON_ARRAYAGG( jo ) AS json_rules
FROM t
GROUP BY customer_id
SELECT *
FROM v_test_rules
| CUSTOMER_ID | JSON_RULES |
|---|---|
| a | [{"trx_id":1,"count_rules":2},{"trx_id":2,"count_rules":2}] |
| b | [{"trx_id":3,"count_rules":3}] |