Converting JSON Booleans to SQL Values

JSON Booleans in SQL

JSON has native true and false values, but SQL databases represent booleans differently depending on the dialect. Understanding these mappings ensures correct data insertion.

Example JSON

{
  "id": 1,
  "name": "Alice",
  "active": true,
  "verified": false,
  "admin": true
}

PostgreSQL Output

INSERT INTO users (id, name, active, verified, admin)
VALUES (1, 'Alice', TRUE, FALSE, TRUE);

PostgreSQL has a native BOOLEAN type that accepts TRUE, FALSE, 't', 'f', 'yes', 'no', 1, and 0.

MySQL Output

INSERT INTO users (id, name, active, verified, admin)
VALUES (1, 'Alice', 1, 0, 1);

MySQL's BOOLEAN is an alias for TINYINT(1). Convention uses 1 for true and 0 for false.

SQL Server Output

INSERT INTO users (id, name, active, verified, admin)
VALUES (1, 'Alice', 1, 0, 1);

SQL Server uses the BIT type with values 1 and 0.

SQLite Output

INSERT INTO users (id, name, active, verified, admin)
VALUES (1, 'Alice', 1, 0, 1);

SQLite has no native boolean type. Booleans are stored as integers: 0 (false) and 1 (true).

Comparison Table

Database	Type	True	False
PostgreSQL	BOOLEAN	TRUE	FALSE
MySQL	TINYINT(1)	1	0
SQL Server	BIT	1	0
SQLite	INTEGER	1	0

Boolean Columns in CREATE TABLE

When generating a CREATE TABLE alongside INSERT, the converter maps JSON booleans to the appropriate column type:

-- PostgreSQL
CREATE TABLE users (
    active BOOLEAN NOT NULL DEFAULT FALSE
);

-- MySQL
CREATE TABLE users (
    active TINYINT(1) NOT NULL DEFAULT 0
);

Edge Case: Boolean Strings

Some JSON payloads encode booleans as strings ("true", "yes", "1"). The converter can optionally detect these patterns and convert them to proper SQL boolean values rather than string literals.