Date Formatting with Marklogic Optic API

Question

My question is about getting the MarkLogic query console javascript API to format a column of strings to dates.

Working on a string directly works as expected:

var d = new Date("3/12/2019");
xdmp.monthNameFromDate(xs.date(d))
>>> March

Working with the optic api however:

const op = require('/MarkLogic/optic');

const ind = op.fromView('schema', 'money');
//get non null dates, stored as strings, [MM-DD-YYYY]
const ind2 = ind.where(op.ne(op.col('completed date'), ""))
const testMonth = op.as('testDate', fn.formatDate(xs.date(op.col('completed date')), "[M01]-[D01]-[Y0001]"))

Returns the following error:

[javascript] XDMP-CAST: function bound ()() -- Invalid cast: {_expr:"¿\"completed date\"", _preplans:null, _schemaName:null, ...} cast as xs.date

I believe this is different than the other questions on this topic because those didn't involve the OPTIC API as far as I can tell, and were resolved by just operating on single strings.How to convert string to date type in MarkLogic? I need to take an optic "column" and convert its type to a date object so I can call the https://docs.marklogic.com/xdmp.monthNameFromDate and other related tools on it.

I feel missing something very straightforward about applying functions to row sets and selecting specific columns.

What I naturally want to do is apply a function to each property of the resulting row set:

let formatted = resulting_rows.map(x=>Date(x['completed date'])

or whatever. This is basically what I do client side, but it feels incorrect to just throw away so much of the built-in javascript functionality and do this all in the browser, especially when I need to do groups on years and months from these views.

It doesn't help that some links about operating on objects are broken: https://docs.marklogic.com/map.keys

Answer 1

The op.as() call defines a dynamic column based on an expression that's applied to each row when the query is executed.

The expression can only use calls to functions provided by the Optic API. In particular, where xs.date() executes when called, op.xs.date() executes when each row is processed. Similarly fn.formatDate() executes immediately while op.fn.formatDate() executes during row processing.

To use the dynamic column, provide it as an argument to op.select(), similar to the following sketch:

op.fromView('schema', 'money');
  .where(op.ne(op.col('completed date'), ""))
  .select([
     op.col('completed date'),
     op.as('testDate', op.fn.formatDate(
         xdmp.parseDateTime(
             op.col('completed date'),
             "[M01]/[D01]/[Y0001]"),
         "[M01]-[D01]-[Y0001]"))
     ])
  .result();

The call to .result() executes the query pipeline.

The map is an XQuery equivalent to JavaScript literal that's not used in sever-side JavaScript. Optic does support a map() pipeline step, which takes a lambda and appears in the pipeline step immediately before the call to result() as documented in:

http://docs.marklogic.com/AccessPlan.prototype.map

Belated footnote: One alternative for this case to parsing and formatting the date would be to use op.fn.translate() to transform the column value by turning every instance of "/" into "-"

Hoping that helps,