How to use Oracle sql with json_object to generate json in NiFi

Question

I am trying to use Oracle JSON_OBJECT to write a query to generate a specific json format.

Here is a sample query (the real query is more complex):

SELECT JSON_OBJECT('name' value name)
  FROM table_a
 WHERE name = 'John'

The query is working in Oracle. I used ExecuteSQL processor and put this query in it. It shows

illegal character in JSON_OBJECT('name' value name)

Any suggestions?

Answer 1

You most likely need to give the column an alias. Nifi probably doesn't like having spaces, single quotes, or parenthesis in the column name.

select json_object('name' value NAME) as json_with_name
from table_a
where NAME = 'John';