JSON do SQL: příkazy INSERT, návrh tabulek a úskalí mapování typů

Stručně: než navrhnete tabulky, rozhodněte, jakou obchodní entitu představuje jeden řádek, a poté napište explicitní víceřádkový VALUES uvnitř jedné transakce; názvy polí a mapování typů vyřešte před importem, místo abyste je záplatovali v SQL.

JSON popisuje tvar; SQL popisuje také omezení. Když přenesete první do druhého, každé omezení, které jste nezapsali, se později vrátí jako špinavá data.

1. Rozhodněte nejprve: jedna tabulka, nebo jeden záznam na řádek JSON

Podívejte se na nejvyšší vrstvu dat před návrhem jakékoli tabulky. Špatné rozhodnutí znamená, že každý následující INSERT bude nutné přepsat.

Kritérium je jen jedno: jakou obchodní entitu představuje jeden řádek? Pokud je řádek objednávkou, položky musí žít ve vlastní tabulce. Pokud je řádek uživatelem, tagy patří do podřízené tabulky nebo sloupce JSON, nikoli do řetězce spojeného čárkami.

2. Od pole k INSERT: nejmenší spustitelná šablona

Nejbezpečnější forma vždy uvádí názvy sloupců explicitně a nikdy se nespoléhá na pořadí sloupců:

复制CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO users (id, name, email) VALUES
  (1, 'Ada', 'ada@example.com'),
  (2, 'Linus', NULL);

Důležité jsou tři detaily:

• Napište každý název sloupce. Když schéma později získá nový sloupec, nic se neposune do špatného pole.
• Víceřádkový VALUES funguje v SQLite, PostgreSQL i MySQL a ve srovnání se smyčkou jednorádkových insertů ušetří velké množství round tripů.
• NULL je klíčové slovo; nedávejte ho do uvozovek. Zápis 'NULL' uloží čtyřznakový řetězec.

Pokud klíče JSON neodpovídají názvům sloupců (userId versus user_id), přidejte mapovací vrstvu. Nehádejte pomocí COALESCE uvnitř SQL. Oprava názvů polí v datové vrstvě je mnohem levnější než záplatování v dotazech.

3. Vnořené objekty: rodičovská tabulka plus podřízená tabulka

Objednávky jsou klasickým případem jedna k mnoha:

复制INSERT INTO orders (id, customer_name, total_cents) VALUES
  (1001, 'Ada', 12900);

INSERT INTO order_items (order_id, sku, quantity) VALUES
  (1001, 'A-1', 2),
  (1001, 'B-7', 1);

To odpovídá JSON {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]}.

Klíčové body:

• Délka pole items určuje počet podřízených řádků; rodičovská tabulka dostane přesně jeden řádek.
• Peníze ukládejte jako celé centy (total_cents), nikdy jako FLOAT.
• Podřízené tabulce dejte cizí klíč order_id a oba inserty potvrďte v jedné transakci, jinak může rodič existovat, zatímco jeho položky chybí.
• Pokud jsou data jen pro čtení a nikdo nefiltruje podle položky, je přijatelný sloupec JSON (PostgreSQL jsonb, MySQL JSON nebo SQLite TEXT s funkcemi json1), ale přijměte cenu: žádné indexování a neohrabané dotazy.

Pevné vnořené objekty jako user.address zploštěte do sloupců (address_city, address_zip). Rozdělte do tabulky jen tehdy, když vnoření může dál růst; dělení pevného tvaru jen přidává JOINy.

4. NULL versus chybějící pole: SQL nemá „chybějící klíč“

JSON umožňuje, aby klíč úplně chyběl. Sloupce SQL jsou pevné. Po importu oba případy vypadají stejně – NULL – i když mohou znamenat něco jiného:

Dvě časté chyby:

1. Použití prázdného řetězce místo NULL. Později WHERE email IS NULL tyto řádky mine a vaše metrika „uživatelé bez e-mailu“ tiše podpočítá.
2. Testování pomocí col = NULL. V tříhodnotové logice SQL se NULL = NULL vyhodnotí jako NULL, nikoli jako TRUE. Musíte napsat IS NULL:

复制SELECT COUNT(*) FROM users WHERE email IS NULL;

Pokud potřebujete uchovat, zda klíč vůbec existoval, přidejte booleovský sloupec s příznakem, místo abyste to odvozovali z NULL. Smlouva API by to měla uvádět také; viz Návrh JSON API a pojmenování polí.

5. Citování a escapování: nikdy neskládejte SQL řetězce

Řetězce JSON mohou obsahovat jednoduché uvozovky, dvojité uvozovky, zpětná lomítka, zalomení řádků a dokonce \u0000. U ručně sestavených řetězců jeden vynechaný escape posune zbytek příkazu a v nejhorším případě se stane SQL injection.

Ve standardním SQL se jednoduchá uvozovka escapuje zdvojením:

复制INSERT INTO notes (id, content) VALUES (1, 'It''s a ''quoted'' value');

Produkční kód by měl používat vazbu parametrů a nechat ovladač řešit escapování a typy:

复制// PostgreSQL používá $1/$2, MySQL ?; tento příklad používá node-postgres
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

Ručně escapujte jen tehdy, když musíte exportovat soubor .sql, a to minimálně:

• nahraďte ' za '';
• pokud zachováváte zalomení řádků, ověřte, že cílový databázový systém přijímá víceřádkové řetězcové literály;
• odstraňte \u0000, protože většina databází NUL uvnitř řetězců odmítá;
• MySQL ve výchozím nastavení považuje zpětné lomítko za escape znak, takže \ se musí stát \\, nebo přepněte do standardního režimu.

Dvojité uvozovky z JSON nepotřebují uvnitř SQL řetězce v jednoduchých uvozovkách žádné escapování. Přidání další vrstvy escapování je způsob, jak do dat dostanete bloudící zpětná lomítka.

6. Mapování typů: kam v SQL přistanou šest hodnot JSON

JSON má jen řetězec, číslo, boolean, null, objekt a pole. SQL má mnohem jemnější typový systém. Chyby se obvykle stávají v přesnosti a sémantice, nikoli v syntaxi:

Booleany skrývají nejjemnější chybu: JSON false se v MySQL TINYINT(1) stane 0, obousměrný převod vypadá dobře a nikdo si nevšimne. Ale ve chvíli, kdy nějaký kód udělá if (value) na řetězci "false", vyhodnotí se jako pravda. Převodní skripty by měly volat Boolean(value) explicitně.

PostgreSQL JSONB parsuje a odstraňuje duplicitní klíče, přičemž ponechá poslední výskyt, což se hodí pro dotazování; JSON zachovává původní text a pořadí klíčů. SQLite JSON je jen TEXT plus sada funkcí, bez skutečného typového omezení. Před volbou se zeptejte, zda tato tabulka potřebuje filtrování podle polí uvnitř JSON.

7. Hromadné inserty a transakce: dávkujte, potvrďte, udělejte opakovatelné

S rostoucím objemem rozhodují o kontroleelnosti importu tři věci:

1. Dávkování. Smyčka N jednorázových INSERT stojí N síťových round tripů. Spojte 500 až 1000 řádků do jednoho víceřádkového VALUES, nebo použijte vlastní hromadný zavaděč databáze (PostgreSQL COPY, MySQL LOAD DATA, víceřádkové inserty uvnitř transakce SQLite).
2. Použijte transakci. Vše, nebo nic, aby polovičně načtená tabulka nikdy nepřežila chybu:

复制BEGIN;
INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Linus');
INSERT INTO order_items (order_id, sku, quantity) VALUES (1, 'A-1', 2);
COMMIT;

3. Udělejte ji idempotentní. Importní skripty se neustále spouštějí znovu. Místo delete-then-insert použijte upsert:

ON CONFLICT vyžaduje jedinečné omezení nebo primární klíč, jinak se nikdy nespustí. Pokud neexistuje přirozený klíč, vytvořte jedinečný index na obchodním klíči, například email. Když není žádný klíč, přidejte na straně JSON stabilní id – nikdy se pro idempotenci nespoléhejte na auto-increment id, protože opakované spuštění vloží duplikáty.

8. Rozdíly mezi dialekty na jednom místě

Aby jeden SQL soubor fungoval ve všech třech, vyhněte se alespoň ON CONFLICT, zpětným apostrofům a SERIAL. U projektů napříč databázemi generujte schéma a upsert příkazy zvlášť; nečekejte, že jeden skript bude sedět všude.

9. Tři kontroly po importu

Než smažete zdrojový soubor, ověřte tři věci:

1. Počet řádků. SELECT COUNT(*) FROM users; se má rovnat délce pole JSON. Chybějící řádek obvykle znamená, že poslední záznam byl zkrácen.
2. Sloupec s nejvyšším podílem NULL. Ukazuje na nekonzistentní názvy klíčů nebo nerozbalené vnoření. Vraťte se a zkontrolujte, zda zdroj nepoužívá userId i user_id.
3. Peníze a dlouhé identifikátory. Vyberte maximální a minimální hodnotu a potvrďte, že je nezkrátil ani plovoucí desetinná čárka, ani převod na celé číslo.

Pokud nejprve potřebujete pochopit tabulkový tvar, použijte postup zploštění v článku Převod JSON, CSV a tabulkových dat. Pokud jsou publikem kolegové, kteří chtějí data jen číst, čtyři způsoby převodu JSON do Excelu dají méně práce než postavit databázi.

Proč čísla po importu JSON do SQL ztratí přesnost?

Celá čísla nad 2^53 už přesnost ztratila během JSON.parse v JavaScriptu a zápis do BIGINT ji nemůže obnovit. Taková id mají být v JSON řetězce a během importu se mají převést na BIGINT nebo TEXT.

Co použít jako primární klíč, když JSON nemá id?

Nechte databázi vygenerovat jeden, nebo před importem vytvořte stabilní UUID. Nikdy nepoužívejte index pole jako id: opakované spuštění změní pořadí dat a namíří špatné řádky na špatné záznamy. Jedinečný index na obchodním klíči (e-mail, číslo objednávky) také umožní upserty.

Co dělat, když hromadný INSERT narazí na konflikt jedinečného klíče?

Nahraďte prostý INSERT odpovídajícím upsertem pro váš dialekt a ověřte, že konfliktní sloupec má jedinečné omezení. Smazání a následné vložení také funguje, ale musí proběhnout v jedné transakci a přijímáte krátké období bez řádků.

Potřebují vnořená pole vždy podřízenou tabulku?

Ne. Pro zobrazení jen pro čtení bez dotazování podle podřízených položek je sloupec JSON jednodušší. Jakmile potřebujete filtrovat nebo agregovat podle tagu či SKU, udržitelnou volbou je podřízená tabulka s indexy. Rozhodující je, jak dotazujete, nikoli jak jsou data tvarovaná.