Get required version of content properties via REST API

[Pavel]

1. Create a content property on page with

POST /rest/api/content/{id}/property

{ "key": "example-property-key", "value": { "anything": "goes 1" } }

2. Update the same content property with

PUT /rest/api/content/{id}/property/{key}

{ "key": "example-property-key", "value": { "anything": "goes 2" }, "version": { "number": 2, "minorEdit": false, "hidden": false } }

3. How can I get the property  with values of the 1st version?

GET /rest/api/content/{id}/property/{key}

GET /rest/api/content/{id}/property

These methods return only 2nd version values.

[Diego · Accepted solution]

Hello there Pavel!

First, we need to understand that each time you publish a page, a new database entry is generated in the CONTENT table in Confluence. Each entry has a different contentid value. Also, each content table entry has a entry in CONTENTPROPERTIES table as well. So changing the values of keys will directly affect that table entry in CONTENTPROPERTIES.

Now that we know that, we can try to at least get content properties from previous Page Versions.
Since those CONTENT table entries are "connected" only by the lowertitle and prevver columns, we can use the following SQL Query to get the desired information:

SELECT * 
FROM content c1
JOIN content c2 ON c1.prevver = c2.contentid 
WHERE c1.contenttype LIKE 'PAGE' AND c1.lowertitle = c2.lowertitle AND c1.lowertitle LIKE 'versions' AND c1.content_status LIKE 'current' AND c1.version = 3

The c1.version alias is there so that we get the information for the page version that we need. In this case, I used version 3.
We will now need to check get the ID shown in the query results. Here, I tried to use REST to fetch the content properties for the desired page entry.

At this point, I was unable to fetch the results via REST. The following error message was thrown for me, even though the CONTENTPROPERTIES and CONTENT table entries for the page were there in the database:

"Cannot find content with id ContentId{id=1081400}"

However, we might still be able to fetch this information via SQL with the following query:

SELECT * FROM contentproperties WHERE contentid = 1081400

Here, contentid should be the contentid of the desired page version.

For short:

Changing the values of content properties keys will change the database entry for the current page version and there is no "backup" of this. There are entries of previous page versions that we can fetch via SQL.

Let us know if this sheds some light on your process!