[SOLVED] How to get the uri of the records returned by an optic query

How to get the uri of the records returned by an optic query

I have written an optic query to get a list of documents and I need the URIs of those documents.

let $sql := "select * from table1"
let $results := op:from-sql($sql)

I need the URIs of all the records in $results.

How to achieve this?

Solution

You can get there if you use op:from-view instead. It allows you to provide named id's for system columns, like the op:fragment-id-col, and join them in subsequently. To get all records with all columns and an extra database uri returned you would do something like:

xquery version "1.0-ml";

import module namespace op="http://marklogic.com/optic"
  at "/MarkLogic/optic.xqy";
 
let $fragmentId := op:fragment-id-col("fragmentId") (: name irrelevant, just needs to be unique :)
return op:from-view("myschema", "table1", (), $fragmentId)
  => op:join-doc-uri(op:col("uri"), $fragmentId)
  => op:limit(10)
  => op:result()

Note: running through the entire set of records at once it probably not going to scale well. Best to page through, and get your results in chunks.

I would also like to note that you could improve performance if you avoid the join by simply adding the uri as an extra column to your view. You can populate it using xdmp:node-uri(.) in your TDE template.

HTH!