Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

Is this a bug or am I missing something?

Am using:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0
EDIT:
To add to the mystery:
SELECT json_object(
    'a' value 1,
    'b' value 2,
    'c' value 'blah'
    FORMAT JSON)
FROM dual
gives me:
{"a":1,"b":2,"c":blah}
SELECT json_object(
    'a' value 1,
    'b' value 2,
    'c' value 3
    FORMAT JSON)
FROM dual
gives me:
Error starting at line : 1 in command -
SELECT json_object(
    'a' value 1,
    'b' value 2,
    'c' value 3
    FORMAT JSON)
FROM dual
Error at Command Line : 4 Column : 15
Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
                cheers for this MT0.  It seems its the same in 18c and 21c also.  I guess this is a bug which has remained outstanding for some while, which makes it useless.  Will have to make my own functionality.
– Graham
                Mar 3 at 9:11
                No, it is not a bug. It is intended behaviour (see my answer below) because you are telling Oracle (by using the FORMAT JSON keywords) that the input values are already valid JSON and it does not need to format them. If you want Oracle to format the strings (with surrounding quotes and escaping other quotes) then do not use the FORMAT JSON keywords.
– MT0
                Mar 3 at 9:13
format_clause
Specify FORMAT JSON after an input expression to declare that the value that results from it represents JSON data, and will therefore not be quoted in the output.
When you use FORMAT JSON then Oracle expects the value supplied to be valid JSON and will output the supplied value without applying any formatting to it. Therefore, if it is a string literal then you are expected to supply the surrounding quotes as part of the input value so that it is valid JSON when you use the FORMAT JSON option.
SELECT json_object(
         'a' value 'blah' FORMAT JSON,
         'b' value 'blah' FORMAT JSON,
         'c' value 'blah' FORMAT JSON
       ) AS formatted_values
FROM   DUAL
Outputs:
If you want Oracle to quote the strings then do not use FORMAT JSON:
SELECT json_object(
         'a' value 'blah',
         'b' value 'blah',
         'c' value 'blah'
       ) AS formatted_values
FROM   DUAL
Outputs:
If you do want to use FORMAT JSON then make sure that the output values are valid JSON by providing the quotes:
SELECT json_object(
         'a' value '"blah"' FORMAT JSON,
         'b' value '"blah"' FORMAT JSON,
         'c' value '"blah"' FORMAT JSON
       ) AS formatted_values
FROM   DUAL
STRICT
Specify the STRICT clause to verify that the output of the JSON generation function is correct JSON. If the check fails, a syntax error is raised.
Example 2: No quotes around output string when FORMAT JSON is used.
SELECT JSON_OBJECT ('name' value 'Foo' FORMAT JSON ) FROM DUAL
Output:
                And there was me thinkning the FORMAT JSON applied to the whole object and to it's desired output format.  This also solves the true false values issue that I was trying to solve - cheers for this MT0.
– Graham
                Mar 3 at 9:31
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.