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
10

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

1 Like
11

@vikwato I have tested the above script in DHIS2 Play (https://play.dhis2.org/2.36.) 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
13

Hi @adulojusm and the team,
So, How do you query the values in json format column? I need to edit one value (date/time) that causes my analytics to fail to run