We recently upgraded our instance from 2.30 through to 2.33. After the upgrade all SQL Views that referenced the trackedentitydatavalue table no longer work as the table was removed. The closest we have been able to note is that there is a new jason column called eventdatavalue in the programstageinstance. Question: How can we query this column and match the values to specific Dataelements? Any example sql scripts? Thank you.
Hi @WaluQ ,
You are right, we migrated our trackedentitydatavalue table to jsonb to optimize our lookups for events. It is still possible to query this data using the Postgresql json\jsonb operators:
The datavalues are now stored in the programstageinstance table, in the column eventdatavalues.
The structure of the jsonb is as follows:
{
"<data element uid>": {"value": ..., "created": ..., ...},
"<data element uid>": {...}
}
Example:
Data:
programinstanceid | value
--------------------------
571 | "{""ovY6E8BSdto"": {""value"": ""Positive"", ""created"": ""2020-06-03T11:36:21.144"", ""storedBy"": ""DEMO"", ""lastUpdated"": ""2020-06-03T11:36:30.030"", ""providedElsewhere"": false}}"
SQL to retrieve value where value exists:
SELECT programstageinstanceid, eventdatavalues #>> '{ovY6E8BSdto, value}'
FROM public.programstageinstance
WHERE eventdatavalues ? 'ovY6E8BSdto'
LIMIT 1;
Result:
programinstanceid | value
--------------------------
571 | "Positive"
Hope this helps 
4 Likes