JSON to SQL: INSERT 문, 테이블 설계, 타입 매핑 함정

핵심부터 말하면, 테이블을 설계하기 전에 한 행이 어떤 업무 엔터티를 나타내는지 결정한 뒤, 단일 트랜잭션 안에서 명시적 다중 행 VALUES를 쓰세요. 필드 이름과 타입 매핑은 가져오기 전에 정하고 SQL에서 패치하지 마세요.

JSON은 모양을 설명하고, SQL은 제약도 설명합니다. 전자를 후자로 옮길 때 적어 두지 않은 제약은 나중에 더러운 데이터로 돌아옵니다.

1. 먼저 결정: 하나의 테이블인가, JSON 행마다 하나의 레코드인가

테이블을 설계하기 전에 데이터의 가장 바깥 계층을 보세요. 이 결정을 틀리면 이후 모든 INSERT를 다시 써야 합니다.

기준은 단 하나입니다. 한 행이 어떤 업무 엔터티를 나타내는가? 행이 주문이면 라인 항목은 자체 테이블에 있어야 합니다. 행이 사용자이면 태그는 자식 테이블이나 JSON 열에 속하지, 쉼표로 이은 문자열에 속하지 않습니다.

2. 배열에서 INSERT로: 가장 작은 실행 가능한 템플릿

가장 안전한 형태는 항상 열 이름을 명시적으로 나열하고 열 순서에 의존하지 않습니다:

复制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);

세 가지 디테일이 중요합니다:

• 모든 열 이름을 쓰세요. 나중에 스키마에 열이 늘면 무엇도 잘못된 필드로 밀리지 않습니다.
• 다중 행 VALUES는 SQLite, PostgreSQL, MySQL에서 동작하며, 단일 행 삽입 루프보다 왕복을 크게 줄입니다.
• NULL은 키워드이므로 따옴표를 붙이지 마세요. 'NULL'로 쓰면 네 글자 문자열이 저장됩니다.

JSON 키가 열 이름과 맞지 않으면(userId 대 user_id) 매핑 계층을 더하세요. SQL 안에서 COALESCE로 추측하지 마세요. 데이터 계층에서 필드 이름을 고치는 것이 질의에서 패치하는 것보다 훨씬 저렴합니다.

3. 중첩 객체: 부모 테이블 + 자식 테이블

주문은 고전적인 일대다 사례입니다:

复制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);

이는 JSON {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]}에 대응합니다.

요점:

• items 배열의 길이가 자식 행 수를 결정하고, 부모 테이블은 정확히 한 행을 얻습니다.
• 돈은 정수 센트(total_cents)로 저장하고 결코 FLOAT로 저장하지 마세요.
• 자식 테이블에 order_id 외래 키를 주고 두 삽입을 하나의 트랜잭션에서 커밋하세요. 그렇지 않으면 부모는 존재하는데 항목이 빠질 수 있습니다.
• 데이터가 읽기 전용이고 누구도 라인 항목으로 필터링하지 않는다면 JSON 열도 허용됩니다(PostgreSQL jsonb, MySQL JSON, 또는 json1 함수를 쓰는 SQLite TEXT). 그러나 비용을 받아들이세요. 인덱싱이 없고 질의가 어색합니다.

user.address 같은 고정 중첩 객체는 열(address_city, address_zip)로 평탄화하세요. 중첩이 계속 커질 수 있을 때만 테이블로 분할하세요. 고정 모양을 분할하면 JOIN만 늘어납니다.

4. NULL 대 누락 필드: SQL에는 "없는 키"가 없음

JSON은 키가 완전히 없을 수 있습니다. SQL 열은 고정입니다. 가져온 뒤 두 경우는 NULL로 똑같이 보이지만, 다른 의미일 수 있습니다:

흔한 두 실수:

1. NULL 대신 빈 문자열을 쓰는 것. 나중에 WHERE email IS NULL이 그 행을 놓치고 "이메일 없는 사용자" 지표가 조용히 과소 집계됩니다.
2. col = NULL으로 검사하는 것. SQL의 삼값 논리에서 NULL = NULL은 TRUE가 아니라 NULL로 평가됩니다. 반드시 IS NULL을 쓰세요:

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

키가 존재했는지 자체를 보존해야 한다면 NULL에서 추론하지 말고 불리언 마커 열을 더하세요. API 계약도 이를 밝혀야 합니다. JSON API 설계와 필드 이름을 참고하세요.

5. 인용과 이스케이프: SQL 문자열을 절대 이어 붙이지 말 것

JSON 문자열에는 단일 인용부호, 큰따옴표, 역슬래시, 줄 바꿈, 심지어 \u0000이 포함될 수 있습니다. 직접 만든 문자열에서는 이스케이프 하나를 놓치면 나머지 문이 밀리고, 최악의 경우 SQL 주입이 됩니다.

표준 SQL에서 단일 인용부호는 두 번 써서 이스케이프합니다:

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

운영 코드는 파라미터 바인딩을 쓰고 이스케이프와 타입을 드라이버에 맡겨야 합니다:

复制// PostgreSQL uses $1/$2, MySQL uses ?; this example uses node-postgres
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

.sql 파일을 반드시 내보내야 할 때만 손으로 이스케이프하고, 최소한 다음을 하세요:

• '를 ''로 바꾼다.
• 줄 바꿈을 보존한다면 대상 데이터베이스가 여러 줄 문자열 리터럴을 받아들이는지 확인한다.
• \u0000을 제거한다. 대부분 데이터베이스는 문자열 내부의 NUL을 거부한다.
• MySQL은 기본적으로 역슬래시를 이스케이프 문자로 취급하므로 \를 \\로 바꾸거나 표준 모드로 전환한다.

JSON의 큰따옴표는 SQL 단일 인용 문자열 안에서 이스케이프가 필요 없습니다. 추가 이스케이프 층을 더하는 것이 데이터에 엉뚱한 역슬래시가 남는 원인입니다.

6. 타입 매핑: 여섯 JSON 값이 SQL에서 떨어지는 곳

JSON에는 문자열, 숫자, 불리언, null, 객체, 배열만 있습니다. SQL은 훨씬 정밀한 타입 체계를 가집니다. 실수는 대개 문법보다 정밀도와 의미에서 나옵니다:

불리언은 가장 미묘한 버그를 숨깁니다. JSON true/false가 MySQL TINYINT(1)에서 0/1이 되고 왕복이 멀쩡해 보이며 아무도 알아채지 못합니다. 그러나 어느 코드가 문자열 "false"에 if (value)를 하는 순간 참으로 평가됩니다. 변환 스크립트는 Boolean(value)를 명시적으로 호출해야 합니다.

PostgreSQL JSONB는 키를 파싱하고 중복을 제거해 마지막 값을 유지하므로 질의에 어울립니다. JSON은 원본 텍스트와 키 순서를 보존합니다. SQLite JSON은 TEXT에 함수 집합을 더한 것일 뿐 진짜 타입 제약이 없습니다. 고르기 전에 이 테이블이 JSON 내부 필드로 필터링해야 하는지 물으세요.

7. 대량 삽입과 트랜잭션: 배치, 커밋, 재실행 가능하게

볼륨이 커지면 가져오기를 통제 가능하게 유지하는 세 가지가 있습니다:

1. 배치. N개의 단일 행 INSERT 루프는 N번의 네트워크 왕복을 치릅니다. 500~1000행을 하나의 다중 행 VALUES로 합치거나 데이터베이스 자체의 대량 로더(PostgreSQL COPY, MySQL LOAD DATA, SQLite 트랜잭션 내 다중 행 삽입)를 쓰세요.
2. 트랜잭션을 쓰세요. 전부 아니면 전무라서 절반만 적재된 테이블이 오류 후 살아남지 않습니다:

复制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. 멱등하게 만드세요. 가져오기 스크립트는 끊임없이 재실행됩니다. 삭제 후 삽입 대신 upsert를 쓰세요:

ON CONFLICT는 유일 제약이나 기본 키가 필요하며, 없으면 절대 발동하지 않습니다. 자연 키가 없으면 email 같은 업무 키에 유일 인덱스를 만드세요. 키가 전혀 없으면 JSON 쪽에 안정적인 id를 더하세요. 멱등성에 자동 증가 id를 절대 의존하지 마세요. 재실행하면 중복을 삽입합니다.

8. 방언 차이 한눈에 보기

하나의 SQL 파일이 셋 모두에서 동작하게 하려면 적어도 ON CONFLICT, 백틱, SERIAL을 피하세요. 데이터베이스 간 프로젝트에서는 스키마와 upsert 문을 따로 생성하세요. 하나의 스크립트가 모두에 맞을 거라 기대하지 마세요.

9. 가져온 후 세 가지 표본 점검

소스 파일을 지우기 전에 세 가지를 검증하세요:

1. 행 수. SELECT COUNT(*) FROM users;가 JSON 배열 길이와 같아야 합니다. 행이 하나 빠졌다면 대개 마지막 레코드가 잘린 것입니다.
2. NULL 비율이 가장 높은 열. 키 이름이 고르지 않거나 중첩이 확장되지 않았다는 신호입니다. 원본이 userId와 user_id를 모두 쓰는지 돌아가 확인하세요.
3. 돈과 긴 식별자. 최대와 최소 값을 뽑아 부동소수점이나 정수 변환이 잘라내지 않았는지 확인하세요.

먼저 표 형식을 이해해야 한다면 JSON, CSV, 표 형식 데이터 변환의 평탄화 접근을 쓰세요. 대상이 데이터를 읽기만 하려는 동료라면 JSON을 Excel로 변환하는 네 가지 방법이 데이터베이스를 세우는 것보다 수고가 적습니다.

JSON을 SQL로 가져온 뒤 숫자가 왜 정밀도를 잃나?

2^53을 넘는 정수는 JavaScript의 JSON.parse 중 이미 정밀도를 잃었고, BIGINT에 쓰는 것으로 복원되지 않습니다. 그런 id는 JSON에서 문자열이어야 하고 가져오기 중에 BIGINT나 TEXT로 변환해야 합니다.

JSON에 id가 없을 때 기본 키로 무엇을 써야 하나?

데이터베이스가 생성하게 하거나 가져오기 전에 안정적인 UUID를 만드세요. 배열 인덱스를 id로 절대 쓰지 마세요. 재실행하면 데이터 순서가 바뀌어 잘못된 행이 잘못된 레코드를 가리킵니다. 업무 키(email, 주문 번호)의 유일 인덱스는 upsert도 가능하게 합니다.

대량 INSERT가 유일 키 충돌을 만나면 어떻게 하나?

단순 INSERT를 해당 방언의 upsert로 바꾸고, 충돌 열에 유일 제약이 있는지 확인하세요. 먼저 삭제하고 나중에 삽입하는 방법도 동작하지만, 하나의 트랜잭션 안에서 해야 하고 잠시 행이 없는 창을 받아들여야 합니다.

중첩 배열은 항상 자식 테이블이 필요한가?

아니요. 하위 항목으로 질의하지 않는 읽기 전용 표시에는 JSON 열이 더 간단합니다. 태그나 SKU로 필터링하거나 집계해야 하는 순간 인덱스를 가진 자식 테이블이 지속 가능한 선택입니다. 결정 요인은 데이터 모양이 아니라 질의 방식입니다.