some invalid data in opensrp database event in profile json object

[imukunzi]

Affected App or Server Version

v.1.6.10

What kind of support do you need?

There is invalid data in event profile json object, we want to analyze these data, from the excel data captured in March during testing phase hivrisk contains mixed values, These baseEntiryId can help 81341a9d-c91e-4561-a98c-4939ea77164d | a15ed7bc-af7e-435a-af1b-91c63e9178b1 | 56be1ce3-3dc1-46b4-963c-5861ace933f4 We want to use data from database and value need to be harmonized.

==>2022-05-31
There is some invalid data on Symptoms and followup event data for some rows in database we have have number where we are expecting text. On the same shared data baseEntityId 7f3b062a-e622-4485-83a4-1457e6837d7f Physical symptoms column.

What is the acceptance criteria for your support request?

Harmonized profile data
Harmonized Physical Symptoms data

Relevant Information

No response

[SebaMutuku]

@imukunzi can we have a call with you to see how this is happening?
Can we also get some screen recording or screenshots.

[AngelaKabari]

@junaidwarsivd find a screenshot of this issue here

[dubdabasoduba]

@imukunzi Please share the queries you are running to flatten the data.

[imukunzi]

@dubdabasoduba here are queries
============ profile =====================
select id,json->>'baseEntityId' as baseentityid,trim(both '[""]' from json->'obs'->1->>'humanReadableValues') as "educationlevel",trim(both '[""]' from json->'obs'->2->>'humanReadableValues') as "catchmentarea",trim(both '[""]' from json->'obs'->3->>'humanReadableValues') as "maritalstatus",trim(both '[""]' from json->'obs'->4->>'humanReadableValues') as "occupation",trim(both '[""]' from json->'obs'->5->>'values') as "hiv_risk",trim(both '[""]' from json->'obs'->6->>'humanReadableValues') as "lmpknown",trim(both '[""]' from json->'obs'->7->>'values') as "lmpknowndate",trim(both '[""]' from json->'obs'->23->>'values') as "gestationalage",trim(both '[""]' from json->'obs'->24->>'values') as "gravida",trim(both '[""]' from json->'obs'->25->>'values') as "previouspregnancies",trim(both '[""]' from json->'obs'->26->>'humanReadableValues') as "miscouriage",trim(both '[""]' from json->'obs'->27->>'values') as "livebirths",trim(both '[""]' from json->'obs'->28->>'humanReadableValues') as "last_birth_preterm",trim(both '[""]' from json->'obs'->29->>'values') as "parity",trim(both '[""]' from json->'obs'->30->>'values') as "c_section",trim(both '[""]' from json->'obs'->31->>'values') as "newborndied",trim(both '[""]' from json->'obs'->32->>'values') as "childrenalive",trim(both '[""]' from json->'obs'->35->>'values') as "datelastborn",trim(both '[""]' from json->'obs'->36->>'humanReadableValues') as "lastbornstatus",trim(both '[""]' from json->'obs'->37->>'humanReadableValues') as "prev_preg_complication",trim(both '[""]' from json->'obs'->38->>'values') as "preeclampsiarisk",trim(both '[""]' from json->'obs'->39->>'values') as "gdmrisk",trim(both '[""]' from json->'obs'->40->>'humanReadableValues') as "healthconditions",trim(both '[""]' from json->'obs'->42->>'values') as "hivpositive",trim(both '[""]' from json->'obs'->43->>'humanReadableValues') as "alergies",trim(both '[""]' from json->'obs'->44->>'humanReadableValues') as "surgies",trim(both '[""]' from json->'obs'->45->>'humanReadableValues') as "tt_immunisation",trim(both '[""]' from json->'obs'->49->>'humanReadableValues') as "medications",trim(both '[""]' from json->'obs'->57->>'humanReadableValues') as "partnerhivpositive" from core.event where json->'eventType'='"Profile"' order by id desc;

===========symptoms=================
SELECT id,json->'type' as type_,json->'eventType' as event_type,btrim(json->'obs'->0->>'values','"[]"') as "physical_symptoms",btrim(json->'obs'->1->'values'->>0,'"[]"') as other_symptoms,btrim(json->'obs'->2->'values'->>0,'"[]"') as ipv_signs_symptoms,json->'obs'->3->'values'->>0 as ipv_suspect from core.event where json->'eventType'='"Symptoms and Follow-up"' order by id desc;

==============physical exam==============
SELECT id,json->'baseEntityId' as basebaseentityid,json->'eventType' as event_type,btrim(json->'obs'->0->>'values','"[]"') as "Height",btrim(json->'obs'->1->>'humanReadableValues','"[]"') as "pregest_weight_unknown",btrim(json->'obs'->2->>'values','"[]"') as "current_weight",btrim(json->'obs'->4->>'values','"[]"') as "bmi",btrim(json->'obs'->5->>'values','"[]"') as "gdm_risk",btrim(json->'obs'->6->>'values','"[]"') as "weight_cat",btrim(json->'obs'->7->>'values','"[]"') as "exp_gain",btrim(json->'obs'->8->>'values','"[]"') as "weight_gain_duration",btrim(json->'obs'->9->>'values','"[]"') as "weight_gain",btrim(json->'obs'->10->>'values','"[]"') as "tot_weight_gain",btrim(json->'obs'->11->>'values','"[]"') as "height_cat",btrim(json->'obs'->12->>'values','"[]"') as "bp_systonic",btrim(json->'obs'->13->>'values','"[]"') as "bp_diatolic",btrim(json->'obs'->14->>'values','"[]"') as "hypertension",btrim(json->'obs'->15->>'values','"[]"') as "severe_hypertension",btrim(json->'obs'->16->>'values','"[]"') as "sever_preeclampsia",btrim(json->'obs'->17->>'values','"[]"') as "preeclampsia",btrim(json->'obs'->18->>'values','"[]"') as "body_temp",btrim(json->'obs'->19->>'values','"[]"') as "pulse_rate",btrim(json->'obs'->20->>'humanReadableValues','"[]"') as "pallor",btrim(json->'obs'->21->>'values','"[]"') as "anaemic",btrim(json->'obs'->23->>'values','"[]"') as "muac_value",btrim(json->'obs'->24->>'humanReadableValues','"[]"') as "respiratory_exam",btrim(json->'obs'->25->>'humanReadableValues','"[]"') as "cardiac_exam",btrim(json->'obs'->26->>'humanReadableValues','"[]"') as "breast_exam",btrim(json->'obs'->27->>'humanReadableValues','"[]"') as "abdominal_exam",btrim(json->'obs'->28->>'humanReadableValues','"[]"') as "pervic_exam",btrim(json->'obs'->30->>'humanReadableValues','"[]"') as "cervical_exam",btrim(json->'obs'->31->>'values','"[]"') as "toaster26_hidden",btrim(json->'obs'->32->>'humanReadableValues','"[]"') as "oedema",btrim(json->'obs'->35->>'values','"[]"') as "sfh",btrim(json->'obs'->37->>'humanReadableValues','"[]"') as "fetal_heartbeat",btrim(json->'obs'->39->>'values','"[]"') as "preeclampsia_risk",btrim(json->'obs'->40->>'humanReadableValues','"[]"') as "fetus_position" from core.event where json->'eventType'='"Physical Exam"' order by id desc;

[imukunzi]

SELECT id,json->'baseEntityId' as basebaseentityid,json->'eventType' as event_type,btrim(json->'obs'->4->>'values','"[]"') as "hiv_positive",btrim(json->'obs'->10->>'values','"[]"') as "syphilis_positive",btrim(json->'obs'->16->>'values','"[]"') as "hemoglobine",btrim(json->'obs'->17->>'values','"[]"') as "aneamic" from core.event where json->'eventType'='"Tests"' order by id desc;

[imukunzi]

Remider to add visit/contact number in event json object.

[junaidwarsivd]

hi @imukunzi please apply this querry for the "profile "
select id,
json ->> 'baseEntityId' as baseentityid,
jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "educ_level")') as value ,
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "educ_level")') -> 0 ->> 'humanReadableValues') as "educationlevel",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "marital_status")') -> 0 ->> 'humanReadableValues') as "maritalstatus",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "occupation")') -> 0 ->> 'humanReadableValues') as "occupation",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "hiv_risk")') -> 0 ->> 'values') as "hiv_risk",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "lmp_known")') -> 0 ->> 'humanReadableValues') as "lmpknown",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "lmp_known_date")') -> 0 ->> 'values') as "lmpknowndate",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "gest_age_openmrs")') -> 0 ->> 'values') as "gestationalage",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "gravida")') -> 0 ->> 'values') as "gravida",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "previous_pregnancies")') -> 0 ->> 'values') as "previouspregnancies",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "miscarriages_abortions")') -> 0 ->> 'values') as "miscouriage",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "live_births")') -> 0 ->> 'values') as "livebirths",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "last_live_birth_preterm")') -> 0 ->> 'humanReadableValues') as "last_birth_preterm",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "parity")') -> 0 ->> 'values') as "parity",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "c_sections")') -> 0 ->> 'values') as "c_section",
trim(both '[""]' from json -> 'obs' -> 2 ->> 'humanReadableValues') as "catchmentarea",
trim(both '[""]' from json -> 'obs' -> 31 ->> 'values') as "newborndied",
trim(both '[""]' from json -> 'obs' -> 32 ->> 'values') as "childrenalive",
trim(both '[""]' from json -> 'obs' -> 35 ->> 'values') as "datelastborn",
trim(both '[""]' from json -> 'obs' -> 36 ->> 'humanReadableValues') as "lastbornstatus",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "prev_preg_comps")') -> 0 ->> 'humanReadableValues') as "prev_preg_complication",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "preeclampsia_risk")') -> 0 ->> 'values') as "preeclampsiarisk",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "gdm_risk")') -> 0 ->> 'values') as "gdmrisk",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "health_conditions")') -> 0 ->> 'humanReadableValues') as "healthconditions",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "hiv_positive")') -> 0 ->> 'values') as "hivpositive",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "allergies")') -> 0 ->> 'humanReadableValues') as "alergies",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "surgeries")') -> 0 ->> 'humanReadableValues') as "surgeries",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "tt_immun_status")') -> 0 ->> 'humanReadableValues') as "tt_immunisation",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "medications")') -> 0 ->> 'humanReadableValues') as "medications",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "partner_hiv_positive")') -> 0 ->> 'values') as "partnerhivpositive"
from core.event trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "partner_hiv_positive")') -> 0 ->> 'values') as "partnerhivpositive"
where json -> 'eventType' = '"Profile"'
order by id desc;

Please Note : change these trim(both '[""]' from json -> 'obs' -> 2 ->> 'humanReadableValues') as "catchmentarea",
trim(both '[""]' from json -> 'obs' -> 31 ->> 'values') as "newborndied",
trim(both '[""]' from json -> 'obs' -> 32 ->> 'values') as "childrenalive",
trim(both '[""]' from json -> 'obs' -> 35 ->> 'values') as "datelastborn",
trim(both '[""]' from json -> 'obs' -> 36 ->> 'humanReadableValues') as "lastbornstatus",
to
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[*] ? (@.formSubmissionField == "FORM field NAME ")') -> 0 ->> 'values') as "RELEVANT COLUMN"

updating the rest of the querries as well in this thread cc @AngelaKabari @dubdabasoduba

[junaidwarsivd]

@imukunzi please apply this querry for the symptoms :
SELECT id,
json -> 'type' as type_,
json -> 'eventType' as event_type,
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "phys_symptoms")') -> 0 ->> 'values') as "physical_symptoms",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "other_symptoms")') -> 0 ->> 'values') as "other_symptoms",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "ipv_signs_symptoms")') -> 0 ->> 'values') as "ipv_signs_symptoms",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "ipv_suspect")') -> 0 ->> 'values') as "ipv_suspect"
from core.event
where json -> 'eventType' = '"Symptoms and Follow-up"'
order by id desc;

[junaidwarsivd]

@imukunzi please apply this querry for physical exams:

SELECT id,
json -> 'baseEntityId' as basebaseentityid,
json -> 'eventType' as event_type,
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "height")') -> 0 ->> 'values') as "Height",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "pregest_weight_unknown")') -> 0 ->> 'humanReadableValues') as "pregest_weight_unknown",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "current_weight")') -> 0 ->> 'values') as "current_weight",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "bmi")') -> 0 ->> 'values') as "bmi",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "gdm_risk")') -> 0 ->> 'values') as "gdm_risk",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "weight_cat")') -> 0 ->> 'values') as "weight_cat",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "exp_weight_gain")') -> 0 ->> 'values') as "exp_gain",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "weight_gain_duration")') -> 0 ->> 'values') as "weight_gain_duration",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "weight_gain")') -> 0 ->> 'values') as "weight_gain",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "tot_weight_gain")') -> 0 ->> 'values') as "tot_weight_gain",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "height_cat")') -> 0 ->> 'values') as "height_cat",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "bp_systolic")') -> 0 ->> 'values') as "bp_systonic",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "bp_diastolic")') -> 0 ->> 'values') as "bp_diatolic",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "hypertension")') -> 0 ->> 'values') as "hypertension",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "severe_hypertension")') -> 0 ->> 'values') as "severe_hypertension",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "severe_preeclampsia")') -> 0 ->> 'values') as "severe_preeclampsia",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "preeclampsia")') -> 0 ->> 'values') as "preeclampsia",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "body_temp")') -> 0 ->> 'values') as "body_temp",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "pulse_rate")') -> 0 ->> 'values') as "pulse_rate",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "pallor")') -> 0 ->> 'humanReadableValues') as "pallor",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "anaemic")') -> 0 ->> 'values') as "anaemic",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "muac_value")') -> 0 ->> 'values') as "muac_value",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "respiratory_exam")') -> 0 ->> 'humanReadableValues') as "respiratory_exam",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "cardiac_exam")') -> 0 ->> 'humanReadableValues') as "cardiac_exam",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "breast_exam")') -> 0 ->> 'humanReadableValues') as "breast_exam",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "abdominal_exam")') -> 0 ->> 'humanReadableValues') as "abdominal_exam",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "pelvic_exam")') -> 0 ->> 'humanReadableValues') as "pelvic_exam",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "cervical_exam")') -> 0 ->> 'humanReadableValues') as "cervical_exam",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "toaster26_hidden")') -> 0 ->> 'values') as "toaster26_hidden",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "oedema")') -> 0 ->> 'humanReadableValues') as "oedema",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "sfh")') -> 0 ->> 'values') as "sfh",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "fetal_heartbeat")') -> 0 ->> 'humanReadableValues') as "fetal_heartbeat",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "preeclampsia_risk")') -> 0 ->> 'values') as "preeclampsia_risk",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "fetal_presentation")') -> 0 ->> 'humanReadableValues') as "fetus_position"
from core.event
where json -> 'eventType' = '"Physical Exam"'
order by id desc;

[junaidwarsivd]

@imukunzi please apply this querry for TESTS
SELECT id,
json -> 'baseEntityId' as basebaseentityid,
json -> 'eventType' as event_type,
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "hiv_positive")') -> 0 ->> 'values') as "hiv_positive",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "syphilis_positive")') -> 0 ->> 'values') as "syphilis_positive",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "anaemic")') -> 0 ->> 'values') as "aneamic",
trim(both '[""]' from jsonb_path_query_array(json -> 'obs', '$[] ? (@.formSubmissionField == "hb_result")') -> 0 ->> 'values') as "hemoglobine",
from core.event
where json -> 'eventType' = '"Tests"'
order by id desc;

[junaidwarsivd]

@imukunzi please use this querry for the contact/Visit number
SELECT id,
json -> 'baseEntityId' as basebaseentityid,
json -> 'eventType' as event_type,
json as json,
json -> 'details' -> 'Contact' as contact
from core.event
where json -> 'eventType' = '"Contact Visit"'
order by id desc;

[imukunzi]

@junaidwarsivd @dubdabasoduba @AngelaKabari
I couldn't use these queries as they have errors except the last one for visit number. As for this last query, is not responding to our request, a mother will attend 8 visits/contacts meaning we will have 8 json objects for physical exam for example, having visit number in these objects will help to differentiate these objects of one mother.

[junaidwarsivd]

hi @imukunzi seems like the querries added as a comment got some special characters ignored, you can test the querries from the attached word document

querries.odt

requirement for the Contact visit is noted will be updating that in a while
cc @AngelaKabari @dubdabasoduba