How to filter json in PLSQL?

My data look like:

CLUID                       EVENT_PAYLOAD                                                       STEP    
1998-06-22-23.58.16.792243  {"type":"page","name":"currentAccount/88095C45B7E1D68346905AA7B791F731D7F94715/new-transfer","source":"currentAccount/:id/new-transfer","data":{}}  currentAccount/:id/new-transfer
1997-08-25-18.52.07.994112  {"type":"page","name":"orders/sign/200831527425199","source":"orders/sign(/:orderIds)","data":{}}   orders/sign(/:orderIds)
2000-05-09-20.49.42.573031  {"type":"page","name":"currentAccount/33BF3B031E71719AD30C34588B5832CA8F1EC2D1/orders","source":"currentAccount/:id/orders","data":{}}  currentAccount/:id/orders
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

I want to select only data where EVENT_PAYLOAD type is “page”.

I’ve written:

 SELECT CLUID, STEP--, b.type
 FROM VDS_OWNER.PROTO_BEAN_COUNTER_EVENTS_BASE2
 WHERE (JSON_TABLE(EVENT_PAYLOAD, '$'
                        COLUMNS (type VARCHAR2 PATH '$."type"')
                        )
        ) = 'page'

Help would be appreciated. Thanks!

Answer

If you have properly declared the JSON column, you can just use the dot notation to access a JSON value by key:

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

Alternatively, you can use json_value():

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

Demo on DB Fiddle

Advertisement

Answer