How to extract the data from JSONB column

user_13765663 0
Viewed 89

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'
jsonb select postgresql sql json
1 Answers

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'

Demo on DB Fiddle:

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
user_106767160
Related