PostgreSQL database name is contacts_contact and the column I'm interested in is called "fields" 'fields' column looks like this in my database: And a second record: 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

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":"+27729535770",
      "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":"+27729535770",
      "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.

Advertisement

Answer