PostgreSQL 9.4 and JSONB

Support - Assistance technique
1

Hi

I've noted an increased interest from many DHIS users to store,
deliver, and search source documents (policy, annual performance
plans, and similar) in an integrated manner - for instance, a recent
Request for Proposals for the Office of Standards Compliance in SA
outlined a integrated data & document system.

PostgreSQL 9.4 is now 2-3 weeks from final release, and the main new
feature is the new JSONB data type, ref PG Wiki:

"With the new JSONB data type for PostgreSQL, users no longer have to
choose between relational and non-relational data stores: they can
have both at the same time. JSONB supports fast lookups and simple
expression search queries using Generalized Inverted Indexes (GIN).
Multiple new support functions enables users to extract and manipulate
JSON data, with a performance which matches or surpasses the most
popular document databases. With JSONB, table data can be easily
integrated with document data for a fully integrated database
environment."

Has any other DHIS development or user team considered how to
integrate documents into their overall DHIS system?

Regards from Juba, South Sudan
Calle

···

*******************************************

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg

*******************************************

2

Hi Calle,

I think in this case “document” refers to “JSON document” (simply a JSON structure) rather than text documents such as word/pdf/etc.

That said I think it could definitely be interesting to use DHIS as a document repository. Two approaches might work:

  • use the “resource” feature (Apps > Reports > Resource) to upload and store text documents, in combination with the sharing solution for access control. Resources (documents) are searchable, available for download and also accessible through the web api. We could easily extend this with an app to provide a nicer user interface.

  • in 2.17 we introduce support for long text for data values (12 pages per value) for data capture and also support for text in analytics. This could make the system feasible as a “light-weight cms” where you could store text directly in the database. This could give some interesting opportunities for combining the text in various ways in reports, as narratives, as explanations to data, as parts of web pages and so on.

best regards,

Lars

···

On Fri, Nov 21, 2014 at 3:48 AM, Calle Hedberg calle.hedberg@gmail.com wrote:

Hi

I’ve noted an increased interest from many DHIS users to store,

deliver, and search source documents (policy, annual performance

plans, and similar) in an integrated manner - for instance, a recent

Request for Proposals for the Office of Standards Compliance in SA

outlined a integrated data & document system.

PostgreSQL 9.4 is now 2-3 weeks from final release, and the main new

feature is the new JSONB data type, ref PG Wiki:

"With the new JSONB data type for PostgreSQL, users no longer have to

choose between relational and non-relational data stores: they can

have both at the same time. JSONB supports fast lookups and simple

expression search queries using Generalized Inverted Indexes (GIN).

Multiple new support functions enables users to extract and manipulate

JSON data, with a performance which matches or surpasses the most

popular document databases. With JSONB, table data can be easily

integrated with document data for a fully integrated database

environment."

Has any other DHIS development or user team considered how to

integrate documents into their overall DHIS system?

Regards from Juba, South Sudan

Calle

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

3

There was a GSOC project to integrate a more advanced JSON storage, but it hasn’t been completed. From my experience with the students making apps, there are many screaming for better JSON storage capabiltiies when they make apps. I think it would be a great feature to add. The system/user setting is a bit restrictive compared to what a general app developer may want.

But as Lars says, this feature is for JSON structures, not so much for other types of documents…

mobiLars

···

2014-11-21 10:25 GMT+01:00 Lars Helge Øverland larshelge@gmail.com:

Hi Calle,

I think in this case “document” refers to “JSON document” (simply a JSON structure) rather than text documents such as word/pdf/etc.

That said I think it could definitely be interesting to use DHIS as a document repository. Two approaches might work:

  • use the “resource” feature (Apps > Reports > Resource) to upload and store text documents, in combination with the sharing solution for access control. Resources (documents) are searchable, available for download and also accessible through the web api. We could easily extend this with an app to provide a nicer user interface.
  • in 2.17 we introduce support for long text for data values (12 pages per value) for data capture and also support for text in analytics. This could make the system feasible as a “light-weight cms” where you could store text directly in the database. This could give some interesting opportunities for combining the text in various ways in reports, as narratives, as explanations to data, as parts of web pages and so on.

best regards,

Lars

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–

On Fri, Nov 21, 2014 at 3:48 AM, Calle Hedberg calle.hedberg@gmail.com wrote:

Hi

I’ve noted an increased interest from many DHIS users to store,

deliver, and search source documents (policy, annual performance

plans, and similar) in an integrated manner - for instance, a recent

Request for Proposals for the Office of Standards Compliance in SA

outlined a integrated data & document system.

PostgreSQL 9.4 is now 2-3 weeks from final release, and the main new

feature is the new JSONB data type, ref PG Wiki:

"With the new JSONB data type for PostgreSQL, users no longer have to

choose between relational and non-relational data stores: they can

have both at the same time. JSONB supports fast lookups and simple

expression search queries using Generalized Inverted Indexes (GIN).

Multiple new support functions enables users to extract and manipulate

JSON data, with a performance which matches or surpasses the most

popular document databases. With JSONB, table data can be easily

integrated with document data for a fully integrated database

environment."

Has any other DHIS development or user team considered how to

integrate documents into their overall DHIS system?

Regards from Juba, South Sudan

Calle

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Kristian Roland

Research Fellow, Department of Informatics, University of Oslo

Email: lars@roland.bz - roland@ifi.uio.no

Phone: +47 90733036

4

Yes the JSONB data type is interesting as it provides "native" json support
(binary format) and indexing of json structures.

5

I guess postgresql is trying to compete here with the likes of mongodb.

The jsonb storage is interesting. For example storing metadata as a versioned document has lots of possibilities.

Of course any use of this kind of feature would imply a firmer commitment to postgresql over our current neutrality. Which is not necessarily all bad, but it does have consequences.

···

On 21 November 2014 09:31, Lars Helge Øverland larshelge@gmail.com wrote:

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Yes the JSONB data type is interesting as it provides “native” json support (binary format) and indexing of json structures.