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

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?

CodePudding user response：

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';