Postgres column contains string values which need to pivoted-CodePudding

details column has string data in the following format (enclosed by parenthesis as shown) -

{"id":"350876","Time":"Aug 22 2022, 12:41:57 PM" ,"Session":"NO","teamPercentage":89}

how do I add these id , Time, Session , teamPercentage as new columns in the same row?

My thought process: Do I need to do some pivot of some sort? But I don't know if pivot can be done with string values, and more specifically how to detect id, time, session etc. and pivot these values.

CodePudding user response：

This seems to be a JSON value, so just treat it as one. Then you can use the JSON functions to extract the values for the keys

select details::json ->> 'id' as id,
       details::json ->> 'Time' as time,
       details::json ->> 'Session' as session,
       details::json ->> 'teampPercentage' as percentage
from the_table;