Trackedentitydatavalue table in V2.33

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>": {...}



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'


programinstanceid | value
571	              | "Positive"

Hope this helps :slight_smile: