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?

EJ Egyed · Accepted Answer · 2022-02-24 17:13:58Z

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

answered Feb 24, 2022 at 17:13

EJ Egyed

6,1341 gold badge11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Perhaps... but why would the error mention an "illegal character"?