Table storing Tracker datavalues

Which Table stores Tracker datavalues for dhis2 version 2.33

1 Like

programstageinstance.eventdatavalues = stores the data for a particular stage in JSON format.
trackedentityinstance = stores the tracked entity value which are referenced by above table.

Thanks @adulojusm

In that case, how would someone write an sql script to extract data for a particular program stage?

Get the program stage UUID, with this you can filter the table, but you must know how to read JSON datatype in PostgreSQL.

Cheers

@adulojusm , I thought you would provide a sample sql script

@kwebihaf , did you manage to figure this out using PostgreSQL?
@vikwato this is the only place I can find a similar discussion.

@WaluQ , I just located the table but didn’t do any SQL script on it. So I didn’t figure it out.
May be the community may help on the SQL script

Cool, trying to work on it, will update when done…

This worked for me:

SELECT data.key as de_uid,
cast(data.value::json → ‘value’ AS VARCHAR) AS de_value
FROM programstageinstance psi
JOIN json_each_text(eventdatavalues::json) data ON TRUE

2 Likes

Thank you @vikwato, that works, building up from here.

1 Like

@vikwato I have tested the above script in DHIS2 Play (DHIS 2 Demo - Sierra Leone and it’s not working.

The message is Could not execute SQL

The script makes reference to protected tables so you can’t use it in SQL Views. Only through an external database client

1 Like