By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE discount_codes(code INT, active VARCHAR2(4000) check (active is JSON) );
INSERT INTO discount_codes
SELECT 1234,'{"active":"Y"}' FROM dual UNION ALL
SELECT 2468,'{"active":"N"}' FROM dual UNION ALL
SELECT 1359,'{"active":"Y"}' FROM dual
3 rows affected
SELECT d.code
FROM discount_codes d
CROSS JOIN
JSON_TABLE(active, '$' COLUMNS (
active VARCHAR2(100) PATH '$.active'
)) j
WHERE j.active = 'Y'
| CODE |
|---|
| 1234 |
| 1359 |