By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with t(found_in_mart, json_data) as (
select 0, '{name:{moduleCodesSelected:[8, 3], vdps:{contactchannel:1}}' from dual union all
select 1, '{name:{moduleCodesSelected:[7, 0, 1], vdps:{contactchannel:0}}' from dual
)
select found_in_mart,
json_value(json_data, '$.name.moduleCodesSelected[0]') as mod_1,
json_value(json_data, '$.name.moduleCodesSelected[1]') as mod_2,
count(*)
from t
group by
found_in_mart,
json_value(json_data, '$.vdps.contactchannel'),
json_value(json_data, '$.name.moduleCodesSelected[0]'),
json_value(json_data, '$.name.moduleCodesSelected[1]')
| FOUND_IN_MART | MOD_1 | MOD_2 | COUNT(*) |
|---|---|---|---|
| 0 | 8 | 3 | 1 |
| 1 | 7 | 0 | 1 |
with t(found_in_mart, json_data) as (
select 0, '{name:{moduleCodesSelected:[8, 3], vdps:{contactchannel:1}}' from dual union all
select 1, '{name:{moduleCodesSelected:[7, 0, 1], vdps:{contactchannel:0}}' from dual
)
SELECT
FOUND_IN_MART,
JSON_VALUE(JSON_DATA, '$.name.moduleCodesSelected[0]') as mod_1,
JSON_VALUE(JSON_DATA, '$.name.moduleCodesSelected[1]') as mod_2,
JSON_VALUE(JSON_DATA, '$.name.moduleCodesSelected[2]') as mod_3,
COUNT(*)
FROM t
GROUP BY
FOUND_IN_MART,JSON_VALUE(JSON_DATA, '$.vdps.contactchannel'),
JSON_VALUE(JSON_DATA, '$.cssr.moduleCodesSelected[0]') ,
JSON_VALUE(JSON_DATA, '$.cssr.moduleCodesSelected[1]'),
JSON_VALUE(JSON_DATA, '$.cssr.moduleCodesSelected[2]')
ORA-00979: not a GROUP BY expression