Table storing Tracker datavalues

Support - Assistance technique
#1

Which Table stores Tracker datavalues for dhis2 version 2.33

1 Like
#2

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

#3

Thanks @adulojusm

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

#4

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

Cheers

#5

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

#6

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

#7

@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

#8

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

#9

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
Eventdatavalues not show in SQL-VIEW in 2.36.3
#10

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

1 Like
#11

@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

#12

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

1 Like