0

When I run the below SQL query in Oracle db, the value is returning even though where clause is failing, how to avoid this issue?

SELECT
    '{"load_invoice":'
    || JSON_OBJECTAGG(KEY 'Module' VALUE 'AR')
    || '}'
FROM
    dual
WHERE
    1 = 2;
Damo
  • 31
  • 7
  • And this indeed returns a string albeit the contradiction??? I cannot really imagine for an Oracle database (not that I'm too fond of the company, but they are too long in the business already for such a primitive desaster...). If it occurs on another query show that one, it's more likely that you overlooked something on writing the condition. – Aconcagua May 09 '23 at 06:48
  • An aggregate query will always return at least one row even when aggregating no data. For example `SELECT sum(1) FROM dual WHERE 1 = 2;` Use HAVING to filter the result – Serg May 09 '23 at 07:10

2 Answers2

2

You are using an aggregate FUNCTION without GROUP BY:

SELECT
    '{"load_invoice":'
    || JSON_OBJECTAGG(KEY 'Module' VALUE 'AR')
    || '}'
FROM
    dual
WHERE
    1 = 2
GROUP BY 1
;

returns an empty result. The presence of the GROUP BY changes how the filtering is applied: generating the execution plan will show you the difference. It's a funny situation, but quite artificial and doing that on a "real" table with more columns in the select will return an ORA-00937.

p3consulting
  • 2,721
  • 2
  • 12
  • 10
2

As @p3consulting mentioned in their answer, the problem is that you are aggregating over the entire result set and the aggregation function will return a row even if there are no results in the result set.

Consider:

SELECT COUNT(*) FROM DUAL WHERE 1 = 2;

You would expect that to return a single row with a count of 0 (rather than not returning any rows); your query will be similar and return one row with a NULL value for the aggregation.

You have an additional problem in that you are building the outer JSON using string concatenation so the generated JSON is invalid.

What you need to do is use JSON functions throughout:

SELECT JSON_OBJECT(
         KEY 'load_invoice' VALUE JSON_OBJECTAGG(KEY 'Module' VALUE 'AR')
       ) AS invoice
FROM   DUAL
WHERE  1 = 2;

Which outputs:

INVOICE
{"load_invoice":null}

If you want to return zero rows then use HAVING instead of WHERE:

SELECT JSON_OBJECT(
         KEY 'load_invoice' VALUE JSON_OBJECTAGG(KEY 'Module' VALUE 'AR')
       ) AS invoice
FROM   DUAL
HAVING 1 = 2;

Which outputs nothing.

fiddle

MT0
  • 143,790
  • 11
  • 59
  • 117