Is it possible to index a document property and add it as a column in a SQL view?
We are replicating some data from a relational database to marklogic and would like to verify if the data is replicated properly. We wanted to create a view to compare the data.
Unfortunately, we don't store the last updated timestamp element in the document, but it is available as a document property. Is there a way to index the properties of documents and add that as a column for a view?
This might be too disruptive, but you could create a script to update the documents to copy the property timestamp to an element or attribute value and - depending on how many documents need updating - run it in batches on the Task Server using xdmp:spawn().
Got this answer from Marklogic support.
Unfortunately, views do not provide the ability join the document and property fragments. So it is not possible to add document properties as a column to the view.
Properties are elements, so you can create a range index on prop:last-modified just like any other element. And you could include it in a view.
However it's not clear from the docs whether that will actually work in a SQL view, and my experiment was unsuccessful. You might try asking MarkLogic support to get a definitive answer, and file an RFE if the answer is no.