Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable(
   CLUID         VARCHAR(27) NOT NULL PRIMARY KEY
  ,EVENT_PAYLOAD CLOB CONSTRAINT ensure_json CHECK (EVENT_PAYLOAD IS JSON)
  ,STEP          VARCHAR(32) NOT NULL
);
begin
INSERT INTO mytable(CLUID,EVENT_PAYLOAD,STEP) VALUES ('1998-06-22-23.58.16.792243','{"type":"page","name":"currentAccount/88095C45B7E1D68346905AA7B791F731D7F94715/new-transfer","source":"currentAccount/:id/new-transfer","data":{}}','currentAccount/:id/new-transfer');
INSERT INTO mytable(CLUID,EVENT_PAYLOAD,STEP) VALUES ('1997-08-25-18.52.07.994112','{"type":"page","name":"orders/sign/200831527425199","source":"orders/sign(/:orderIds)","data":{}}','orders/sign(/:orderIds)');
INSERT INTO mytable(CLUID,EVENT_PAYLOAD,STEP) VALUES ('2000-05-09-20.49.42.573031','{"type":"page","name":"currentAccount/33BF3B031E71719AD30C34588B5832CA8F1EC2D1/orders","source":"currentAccount/:id/orders","data":{}}','currentAccount/:id/orders');
INSERT INTO mytable(CLUID,EVENT_PAYLOAD,STEP) VALUES ('1998-07-21-20.50.04.641225','{"type":"event","name":"logout","source":"currentAccount/F2A81A2D982AD8D736E5461808CF8C33FDDC9EEC/transactions","data":{"category":"session","normalized":"currentAccount/:id/transactions","eventValue1":"manual"}}','logout');
end;
/
1 rows affected

select cluid, step, e.event_payload.type
from mytable e
where e.event_payload.type = 'page'


CLUID
STEP
TYPE


1998-06-22-23.58.16.792243
currentAccount/:id/new-transfer
page

1997-08-25-18.52.07.994112
orders/sign(/:orderIds)
page

2000-05-09-20.49.42.573031
currentAccount/:id/orders
page


select cluid, step, json_value(e.event_payload, '$.type')
from mytable e
where json_value(e.event_payload, '$.type') = 'page'


CLUID
STEP
JSON_VALUE(E.EVENT_PAYLOAD,'$.TYPE')


1998-06-22-23.58.16.792243
currentAccount/:id/new-transfer
page

1997-08-25-18.52.07.994112
orders/sign(/:orderIds)
page

2000-05-09-20.49.42.573031
currentAccount/:id/orders
page

CLUID	STEP	TYPE
1998-06-22-23.58.16.792243	currentAccount/:id/new-transfer	page
1997-08-25-18.52.07.994112	orders/sign(/:orderIds)	page
2000-05-09-20.49.42.573031	currentAccount/:id/orders	page