SQL to JSON: NULL, Boolean, and 0/1 Semantics

1. Problem: four values that look similar

SQL NULL represents missing or unknown, JSON null is an explicit JSON value, "null" is text, and 0 is numeric. true and false also cannot be inferred from arbitrary database 0/1 values. A converter needs the declared column type and domain meaning, not one observed driver value.

2. Minimal reproduction: native boolean and TINYINT(1) differ

PostgreSQL can construct a JSON boolean directly. MySQL TINYINT(1) is a common convention, but behavior depends on column definition, server, and driver.

-- PostgreSQL
SELECT json_build_object('enabled', enabled, 'note', note) FROM settings;

-- MySQL
SELECT JSON_OBJECT('enabled', enabled, 'note', note) FROM settings;

-- These differ from the strings '0', '1', and 'null'.

3. Root cause: database and driver layers both affect conversion

NULL participates in SQL's three-valued logic. JSON functions often emit JSON null, but COALESCE, IFNULL, or a driver mapper can change the result. PostgreSQL boolean and MySQL numeric switches can arrive in different JavaScript types.

4. Recommendation: map according to an explicit column contract

Record dialect, declared type, and allowed inputs for each field. Accept true/false for a native boolean; map 0/1 only for a declared TINYINT(1) switch. Preserve or reject unknown strings and numbers instead of silently converting. Map SQL NULL to JSON null unless the API explicitly says to omit the field.

5. Complete code: explicit boolean sources

This TypeScript does not guess driver behavior. The caller declares the source; string "0" is not treated as numeric 0, and unknown values throw.

type BooleanSource = "postgres-boolean" | "mysql-tinyint1";
function toJsonBoolean(value: unknown, source: BooleanSource): boolean | null {
  if (value === null) return null;
  if (source === "postgres-boolean" && typeof value === "boolean") return value;
  if (source === "mysql-tinyint1" && (value === 0 || value === 1)) return value === 1;
  throw new TypeError("Value does not match the declared boolean source");
}
console.log(toJsonBoolean(true, "postgres-boolean")); // true
console.log(toJsonBoolean(0, "mysql-tinyint1")); // false
console.log(toJsonBoolean(null, "mysql-tinyint1")); // null

6. Incorrect approaches

value || false collapses NULL, an empty string, and other falsy values into false. Boolean(value) turns the non-empty string "false" into true. COALESCE(enabled, false) discards a meaningful NULL. Converting every 0/1 column also corrupts counters and status codes.

7. Boundaries: NULL, strings, and driver return types

Keep field absence, SQL NULL, JSON null, empty string, and "null" distinct. A driver may return a numeric database value as a string; do not convert based on content alone. If the API omits null fields, implement that policy explicitly in the serializer.

8. Verification

Prepare true/false, 0/1, NULL, "0", "null", and ordinary integers in both MySQL and PostgreSQL. Inspect database JSON output and driver rows, then assert converter type and value. Also verify whether null is JSON null or an omitted property according to the API contract.

9. FAQ

Is MySQL TINYINT(1) boolean? It is a common convention, not a universal semantic; confirm the column, project, and driver contract.

Should NULL become false? Usually preserve JSON null unless the domain explicitly defines a default.

Why is Boolean("false") true? JavaScript checks that the string is non-empty; it does not parse its text.

10. Summary

Preserve the difference between SQL NULL, JSON null, the string null, 0/1, and boolean. Handle PostgreSQL boolean and MySQL TINYINT(1) separately, require an explicit field contract, reject unknown values, and test both dialects with boundary inputs.