We have an vehicle_info stored in JSONB format in vehicle table.
id | vehicle_info(JSONB)
----+------------------------------------------------------------------------------------
1 | {"milestone": {"Honda_car": {"status":"sold"}}
3 | {"milestone": {"Mitsubishi_car", {"status":"available"}}
2 | {"milestone": {"Honda_car", {"status":"available"}}
How do I extract the data which has suffix car.Below is the one that I could think of but ending up in an error.
select * from vehicle where milestone -> LIKE '%_car' ->>'status'
If I followed you correctly, you can enumerate the keys with jsonb_object_keys(), then filter on those that end with '_car', and finally extract the values:
select t.*, t.vehicle_info -> 'milestone' -> k.val ->> 'status' status
from mytable t
cross join lateral jsonb_object_keys(t.vehicle_info -> 'milestone') as k(val)
where k.val like '%_car'
id | vehicle_info | status
-: | :--------------------------------------------------------- | :--------
1 | {"milestone": {"Honda_car": {"status": "sold"}}} | sold
3 | {"milestone": {"Mitsubishi_car": {"status": "available"}}} | available
2 | {"milestone": {"Honda_car": {"status": "available"}}} | available