Empty JSON vs null conversions to a

I'm trying to create an HTTP PUT endpoint in Java that takes in a delta json from the front end and I'm having some trouble figuring out how to implement "nulls".

For example if we have a database model that looks like

id : 1
firstname : Luke
lastname : Xu
age : 24
fav_color : Red

And we send over a PUT request to /person/1 with a json of {age : 25}. Currently, I have a JOOQ pojo to converts to following JSON to a java model but the problem is it is also updating my database values to be null.

There's no difference between

{age : 25}

and

{id : 1,
firstname : null,
lastname : null,
age : 25,
fav_color : null}

Once it hits my Java end point the java model just sets both cases to "null" and there's no difference between a passed in null or the value wasn't being passed in at all.

I also considered processing an input stream (type JSON) but the problem with this is that our JSON names have to be named exactly the same as the database column names which is also kind of unreasonable.

What is the standard for editing the database if we only want to send a "delta json"????

Solution

Since you're using jOOQ, I'd suggest you directly pass the JSON values to the jOOQ UpdatableRecord, which can in fact distinguish between:

null meaning not initialised (or default)
null meaning null

It does so by maintaining a changed() flag for each individual column.

For instance:

{age : 25}

... translates to this Java code:

// record.set(USER.ID, 1) I suspect this is still necessary...?
record.set(USER.AGE, 25);
record.update();

... and to this SQL statement:

UPDATE users
SET age = 25
WHERE id = 1

whereas

{id : 1,
firstname : null,
lastname : null,
age : 25,
fav_color : null}

... translates to this Java code

record.set(USER.ID, 1);
record.set(USER.FIRSTNAME, null);
record.set(USER.LASTNAME, null);
record.set(USER.AGE, 25);
record.set(USER.FAV_COLOR, null);

... and to this SQL statement

UPDATE users
SET firstname = null,
    lastname = null,
    age = 25,
    fav_color = null
WHERE id = 1