Find the last element in a JSON array in Postgres 9.4

Question

We had an outdated system that tried to track all versions of data stored in a particular document. We originally saved our JSON as a string in the old version of Postgres, but we recently upgraded to Postgres 9.3 and we started using the JSON column type.

We had a column called "versions" and it contained an array, and each saved version of a particular document was stored in an array, so a query like this:

SELECT _data_as_json FROM measurements WHERE id = 3307551 

returns JSON as follows:

  {"reports": {}, "versions": [ {"timestamp": "2014-04-28T19:12:31.567415", "user": 11327, "legacy": {}, "vd_version": 1}, {"timestamp": "2014-05-12T18:03:24.417029", "user": 11331, "legacy": {"lengthmoment": {"moment": {"size": 130}, "length": {"in": 64.0}}, "comments": "", "custom": null}, "vd_version": 1}, {"timestamp": "2014-05-12T21:52:50.045758", "user": 10373, "legacy": {"lengthmoment": {"moment": {"size": 130}, "length": {"in": 64.0}}, "comments": "", "custom": null}, "vd_version": 1}, {"timestamp": "2014-05-14T23:34:37.797822", "user": 10380, "legacy": {"lengthmoment": {"moment": {"size": 130}, "length": {"in": 64.0}}, "comments": "", "custom": null}, "vd_version": 1}, {"timestamp": "2014-07-16T14:56:38.667363", "user": 10374, "legacy": {"lengthmoment": {"moment": {"size": 130}, "length": {"in": 64.0}}, "comments": "", "custom": null}, "vd_version": 1}, {"timestamp": "2014-07-16T14:57:47.341541", "user": 10374, "legacy": {"lengthmoment": {"moment": {"size": 130}, "length": {"in": 64.0}}, "comments": "", "custom": null}, "vd_version": 1}, {"timestamp": "2014-07-17T16:32:09.067026", "user": 11331, "legacy": {"lengthmoment": {"moment": {"size": 130}, "length": {"in": 64.0}}, "comments": "", "custom": null}, "vd_version": 1}, {"timestamp": "2014-09-11T14:35:44.436886", "user": 11331, "legacy": {"lengthmoment": {"moment": {"size": 130}, "length": {"in": 64.0}}, "comments": "", "custom": null}, "vd_version": 1}, {"timestamp": "2014-10-15T14:30:50.554932", "user": 10383, "legacy": {"lengthmoment": {"moment": {"size": 130}, "length": {"in": 64.0}}, "comments": "", "custom": null}, "vd_version": 1}, {"timestamp": "2014-10-29T15:36:35.183787", "user": 11331, "legacy": {"lengthmoment": {"moment": {"size": 130}, "length": {"in": 64.0}}, "comments": "", "custom": null}, "vd_version": 1}, {"timestamp": "2014-11-12T22:22:03.892484", "user": 10373, "legacy": {"lengthmoment": {"moment": {"size": 130}, "length": {"in": 64.0}}, "comments": "", "custom": null}, "vd_version": 1} ]} 

We (tried) to save the data in the “versions” in chronological order, but in 99% of cases we only need the last document. In Postgres 9.3, we received this request to get the last item:

 SELECT json_array_elements(_data_as_json->'versions') FROM measurements WHERE id = 3307551 LIMIT 1 OFFSET (SELECT json_array_length(_data_as_json->'versions') - 1 FROM measurements WHERE id = 3307551) 

It basically works, but a little fragile. If we can never properly arrange things in an array of versions, we will return the wrong version of the document. I am curious if there is a better way to do this? I read that Postgres 9.4 offers more features for working with JSON.

Ideally, we could do ORDER BY at the timestamp. Is it possible?

Answer 1

Postgres 9.5 +

The job is simple now, as the manual quotes:

Operators extracting fields / elements / paths that accept integer JSON array indexes support negative feed from the end of arrays .

My bold accent. So for json or jsonb :

 SELECT _data_as_json->'versions'->>-1 FROM measurements m WHERE id = 3307551; 

Postgres 9.4

You can use jsonb instead of json . Use jsonb_array_elements() or jsonb_array_length() respectively.

There is also a more general approach to getting the last item according to the original sort order using the new WITH ORDINALITY (possibly a slower one):

 SELECT v FROM measurements m , jsonb_array_elements(m._data_as_json->'versions') WITH ORDINALITY v(v, rn) WHERE m.id = 3307551 ORDER BY v.rn DESC LIMIT 1; 

Details for WITH ORDINALITY (and implicit JOIN LATERAL in both versions):

• PostgreSQL unsest () with item number

Postgres 9.3

"last" according to the timestamp value:

 SELECT jv FROM measurements m , json_array_elements(m._data_as_json->'versions') j(v) WHERE m.id = 3307551 ORDER BY (jv->>'timestamp')::timestamp DESC LIMIT 1; 

"last" according to ordinal position in json array (faster):

 SELECT _data_as_json->'versions' ->(json_array_length(_data_as_json->'versions') - 1) FROM measurements WHERE id = 3307551; 

We need - 1 , because JSON arrays start at offset 0.

SQL Fiddle