How to query Json field so the keys are the column names and the values are the column fields

PostgreSQL database name is contacts_contact and the column I’m interested in is called “fields”

‘fields’ column looks like this in my database:

{
   "Monthly_household_income":{
      "text":"< $1500"
   },
   "Survey_consent":{
      "text":"Yes"
   },
   "Are_you_working":{
      "text":"No"
   },
   "Network_Provider":{
      "text":"Vodacom"
   },
   "Do_you_own_a_phone":{
      "text":"Family Member"
   },
   "Whatsapp_consent":{
      "text":"TRUE"
   },
   "Highest_education":{
      "text":"< 12"
   },
   "Preferred_channel":{
      "text":"SMS"
   },
   "Identification_type":{
      "text":"sa_id"
   },
   "7d91a89f-6cf7-41e3-817e-718caab32b38":{
      "text":"[phone]",
      "number":2777777777
   },
   "e26acde6-87b3-43eb-bc8d-68df3747e206":{
      "text":"Single"
   },
   "What_would_make_you_stay_on_whatsapp":{
      "text":"Free-Wifi"
   },
   "Why_did_you_switch_from_wa_to_sms":{
      "text":"Cost"
   },
   "How_do_you_charge_your_phone":{
      "text":"Electricity"
   },
   "Prebirth_messaging":{
      "text":"1",
      "number":1
   },
   "Completed_research_survey":{
      "text":"TRUE"
   },
   "Estimated_due_date":{
      "text":"2021-06-07T00:00:00Z",
      "datetime":"2021-06-07T00:00:00.000000Z"
   },
   "Started_research_survey":{
      "text":"TRUE"
   },
   "Whatsapp_undelivered_timestamp":{
      "text":"2020-11-11T15:07:28.925133+00:00",
      "datetime":"2020-11-11T17:07:28.925133+02:00"
   },
   "Info_consent":{
      "text":"TRUE"
   },
   "How_much_airtime_do_you_use_for_momconnect_on_whatsapp":{
      "text":"<R0"
   },
   "Facility_code":{
      "text":"328642",
      "number":328642
   },
   "What_type_of_house_do_you_live_in":{
      "text":"Government Housing"
   },
   "ID_number":{
      "text":"9999999999",
      "number":999999999
   },
   "Income_from_grants":{
      "text":"Childcare"
   },
   "Date_of_birth":{
      "text":"1994-04-14T00:00:00Z",
      "datetime":"1994-04-14T00:00:00.000000Z"
   },
   "Messaging_consent":{
      "text":"TRUE"
   },
   "Research_consent":{
      "text":"TRUE"
   },
   "Registration_date":{
      "text":"2020-10-28T10:48:06.771820+02:00",
      "datetime":"2020-10-28T10:48:06.771820+02:00"
   }
}

And a second record:

 {
   "Monthly_household_income":{
      "text":"< $2000"
   },
   "Survey_consent":{
      "text":"Yes"
   },
   "Are_you_working":{
      "text":"No"
   },
   "Network_Provider":{
      "text":"MTN"
   },
   "Do_you_own_a_phone":{
      "text":"Family Member"
   },
   "Whatsapp_consent":{
      "text":"TRUE"
   },
   "Highest_education":{
      "text":"< 12"
   },
   "Preferred_channel":{
      "text":"SMS"
   },
   "Identification_type":{
      "text":"sa_id"
   },
   "7d91a89f-6cf7-41e3-817e-718caab32b38":{
      "text":"[phone]",
      "number":2777777777
   },
   "e26acde6-87b3-43eb-bc8d-68df3747e206":{
      "text":"Single"
   },
   "What_would_make_you_stay_on_whatsapp":{
      "text":"Free-Wifi"
   },
   "Why_did_you_switch_from_wa_to_sms":{
      "text":"Cost"
   },
   "How_do_you_charge_your_phone":{
      "text":"Electricity"
   },
   "Prebirth_messaging":{
      "text":"1",
      "number":1
   },
   "Completed_research_survey":{
      "text":"TRUE"
   },
   "Estimated_due_date":{
      "text":"2021-06-07T00:00:00Z",
      "datetime":"2021-06-07T00:00:00.000000Z"
   },
   "Started_research_survey":{
      "text":"TRUE"
   },
   "Whatsapp_undelivered_timestamp":{
      "text":"2020-11-11T15:07:28.925133+00:00",
      "datetime":"2020-11-11T17:07:28.925133+02:00"
   },
   "Info_consent":{
      "text":"TRUE"
   },
   "How_much_airtime_do_you_use_for_momconnect_on_whatsapp":{
      "text":"<R0"
   },
   "Facility_code":{
      "text":"328642",
      "number":328642
   },
   "What_type_of_house_do_you_live_in":{
      "text":"Government Housing"
   },
   "ID_number":{
      "text":"9999999999",
      "number":999999999
   },
   "Income_from_grants":{
      "text":"Childcare"
   },
   "Date_of_birth":{
      "text":"1994-04-14T00:00:00Z",
      "datetime":"1994-04-14T00:00:00.000000Z"
   },
   "Messaging_consent":{
      "text":"TRUE"
   },
   "Research_consent":{
      "text":"TRUE"
   },
   "Registration_date":{
      "text":"2020-10-28T10:48:06.771820+02:00",
      "datetime":"2020-10-28T10:48:06.771820+02:00"
   }
}

The ‘fields’ column has fields that look like the two JSON data I shared. It is pretty much responses to a survey. Example, {u’Monthly_household_income’: {u’text’: u'< R1500′}, means that the question was “Monthly household income” and the user responded with $1500.

I want the query output to have a column per question and the replies as the fields.

It should look like this:

Monthly_household_income | Survey_consent | Are you working | Network provider as so on as columns
$1500                    | Yes            | No              | Vodacom          
$2000                    | Yes            | No              | MTN

Answer

As the actual value is nested inside another key, the easiest way is to use the #>> operator:

select fields #>> '{Monthly_household_income, text}' as Monthly_household_income,
       fields #>> '{Survey_consent, text}'  as Survey_consent
       fields #>> '{Are_you_working, text}' as Are_you_working
       ....
from the_table;

If you don’t want to type that all the time, create a view that does this.