NiFi QueryRecord processor - select optional JSON attribute

Question

I got several JSON objects going through a QueryRecord processor. I want to extract some attributes from it including some optional ones. The problem is that selecting an optional attribute results in an error saying that the column is not present in the table.

I tried a workaround using RPATH which looked like this:

SELECT is_answered,
       view_count,
       answer_count,
       score,
       last_activity_date,
       creation_date,
       question_id,
       title,
       CASE
         WHEN HAS_PATH(flowfile, '/last_edit_date') THEN
          RPATH(flowfile, '/last_edit_date')
         ELSE
          NULL
       END AS last_edit_date
  FROM flowfile

In this case last_edit_date is an optional attribute. This however throws an error that flowfile column does not exists. I have tried to find another workaround which would not include flowfile in the select statement however I've not been able to find anything. Any ideas on how to solve this issue

Answer 1

The SQL part of QueryRecord uses the content of the FlowFile, so HAS_PATH is looking for that field in the record not the attributes. Instead, use Expression Language (probably the isEmpty() function) to generate that part of the query, maybe something like:

SELECT is_answered, 
  view_count, 
  answer_count, 
  score, 
  last_activity_date, 
  creation_date, 
  question_id, 
  title,
  CAST("${last_edit_date:isEmpty():ifElse(null,${last_edit_date})}" AS DATE) AS last_edit_date
FROM FLOWFILE

I didn't test this but it should give you the general idea of how to use Expression Language to get at attributes and inject the value into the query.