json_group_object can produce invalid JSON

(1) By anonymous on 2025-05-24 19:39:46 [source]

If I try to call json_object with a NULL key, it fails:

sqlite> SELECT id,json_object(key,value) FROM (SELECT 1 AS id, NULL AS key, 2 AS value);
Runtime error: json_object() labels must be TEXT

However, doing so with json_group_object doesn't give an error, but instead just omits the key entirely (which produces invalid JSON):

sqlite> SELECT id,json_group_object(key,value) FROM (SELECT 1 AS id, NULL AS key, 2 AS value) GROUP BY id;
1|{:2}

jsonb_group_object has this problem as well.

Also, is there a way to get an empty object if the key is NULL? In my case, the key and value are from a left join, so if the key is NULL then it's the only one and the value will also be NULL, so I want to ignore that column.

(2) By Bo Lindbergh (_blgl_) on 2025-05-24 21:41:07 in reply to 1 [link] [source]

Why does json_object fail for non-text keys while json_group_object casts them to text?

(3) By Richard Hipp (drh) on 2025-05-24 21:46:54 in reply to 2 [link] [source]

Because nobody noticed the inconsistency for the first 10 years of the existence of those functions, and I dare not change it now for fear of breaking applications that use those functions.

(4) By Bo Lindbergh (_blgl_) on 2025-05-24 22:31:59 in reply to 3 [link] [source]

Ah, the curse of bug compatibility. (Is there an emoji for that?)

(5) By Nuno Cruces (ncruces) on 2025-05-25 09:03:37 in reply to 3 [link] [source]

But does it make sense that they produce invalid JSON? They even set the JSON_SUBTYPE other functions rely on to skip validation.

Hyrum's lay and all, but should anyone really depend on that?

(6.1) By Richard Hipp (drh) on 2025-05-25 11:00:11 edited from 6.0 in reply to 5 [link] [source]

Generating invalid JSON is probably not something that we need to keep backwards compatible. That's why, since check-in 2025-05-24T20:20Z, rows where the label is NULL in json_group_object() are omitted from the resulting object. This is similar to other aggregate functions (ex: sum(), avg()) which silently ignore NULL entries.

I also considered substituting an empty string or a "null" string in cases where the label is NULL, but simply omitting those elements seemed to be more consistent with the behavior of other aggregate functions.

(7) By anonymous on 2025-05-25 18:21:01 in reply to 6.1 [link] [source]

observations from another annon (apologies if these are obvious):

If we look at other backward misfeatures like the double quoted string misfeature, could we consider a compile time option for JSON 'backwards JSON group/etc bug compatibility' that is similar to the double quoted string option?

presuming the code difference isn't that big of course
JSON is now part of the amalgamation, unlike when the invalid JSON behavior was first introduced, bringing it closer to the double quoted string approach.

Could even have default of current behavior and perhaps shifting default in the future to the 'correct' behavior (whatever that is determined to be).

In sum, it should not break old code anymore than the double quoted string misfeature, yes?