Mostly Making oracle easy, mostly:
Search for:
In ORDS

On NULLs and your JSON responses for ORDS REST APIs

2 Mins Read

Question today: why are you showing null vs ” when we GET data from our table?

internal developer

Let’s take a quick look at what they’re after. We’ll create a TABLE, put some data in it (or lack of data), and then do a SELECT * FROM style REST API.

Our Data

CREATE TABLE NULLS (
    ID      INTEGER,
    MESSAGE VARCHAR2(25)
);
 
INSERT INTO NULLS VALUES (
    1,
    'hello'
);
 
INSERT INTO NULLS VALUES (
    NULL,
    'hello null'
);
 
INSERT INTO NULLS VALUES (
    2,
    NULL
);
 
INSERT INTO NULLS VALUES (
    3,
    ''
);

Our REST API

Show me the records that have an actual ID first, then put the nulls at the end.

When we call the API from our browser…

Records ‘2’ and ‘3’ both lack a value for “message”

Why doesn’t ORDS show ” for a null?

The answer is simply because the database returns a null, not an empty string (Docs).

SQLcl & SQLPlus

All ORDS ever sees is null, so it can’t return an empty string, e.g. ”

What are my options?

If you insist on doing something else, you could…

Do something confusing/bad like this:

Using the NVL() function to replace null with an actual value.

For numbers folks will often replace NULLs with Zero (0), but only you can decide if that’s appropriate or dangerous based on your data model and application logic.

Write some code

To actually return an empty string, ” vs null, you’d have to construct the JSON payload yourself and return that. I don’t recommend that.

And since I don’t recommend it, I’m not going to show you how to do that. But I do have a post on generating your own responses.

Otherwise you could put code ‘somewhere in the middle’ to transform the payloads from the REST APIs to re-write the nulls to ”, or…yeah you’re going to write some code, somewhere.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

2 Comments

  1. Leo Reply

    Hi Jeff,

    Is there a way to output a numerically represented boolean expression as an actual boolean?
    I have tried using “{}”, but I get a parsing error.

    For example, in an Employees table, one could have a column is_active (0/1).
    In my select statement from the handler, the transformation into a boolean should ideally occur automatically for a JSON/query type.

    So for a Query like:
    select id,
    surname,
    prename,
    is_active –stored as 0/1 in db, but output as false/true
    from employees

    The output should be like:
    [{“id”: 1, “surname”: “foo”, “prename”: “bar”, “is_active”: true}]

    • thatjeffsmith Post Author

      Native boolean SQL types require a 23ai db and a 23 jdbc driver

      Or…you could generate your own responses.

Write A Comment