0

I have a table by the name food_order in POSTGRESQL, this table contains 3 columns i.e. _id, status, and status_events. Status_events contains a JSON string like below

_id. status. . status_events
12345 Order_delivered [{""status_name"":""order_received"", 
                            ""status_time"":    
                            1632580815}, {""status_name"": 
                             ""order_accepted"", ""status_time"": 
                            1632580880},

I want to split the status_events like below

_id status. status_name_1 status_time1 status_name_2 status_time_2
12345 order_delivered order_received 1634567 order_accepted 16543255

I tried the below option, but getting each status in a separate row SELECT arr.item_object,arr.item_object->>'status_name'as status, arr.item_object->>'status_time'as stime FROM food_order, jsonb_array_elements(status_events) with ordinality arr(item_object, position) ;

Ismaili Mohamedi
  • 906
  • 7
  • 15
Srini Reddy
  • 1
  • 1
  • Does the array always contain exactly two objects? – Bergi May 05 '23 at 13:27
  • You already did the hard part, you only need to [pivot values](https://stackoverflow.com/questions/20618323/create-a-pivot-table-with-postgresql), in this case you can use `position`: [dbfiddle](https://dbfiddle.uk/wVG37Dr0). – Ponder Stibbons May 05 '23 at 16:07

0 Answers0