본문 바로가기
빌드노트 검색

빌드노트

PostgreSQL JSONB에서 뽑은 문자열에 따옴표가 남는 이유

JSONB의 ->는 JSON 값을, ->>는 SQL text를 반환한다. PostgreSQL 18.6의 합성 입력으로 문자열·숫자·JSON null·없는 키·SQL NULL의 결과를 비교하고, 따옴표 처리와 타입 확인, 숫자 캐스팅의 판단 기준을 설명한다.

PostgreSQL에서 JSONB의 doc->'v'를 조회했는데 문자열이 Ada가 아니라 "Ada"로 보일 수 있다. ->가 반환하는 값의 SQL 타입이 jsonb이기 때문이다. 큰따옴표는 JSON 문자열을 표현하는 기호다. 일반 문자열과 비교하거나 문자열 함수에 넘길 값은 doc->>'v'로 SQL text를 꺼낸다.

다만 ->>를 쓰면 JSON null과 없는 키가 모두 SQL NULL로 나온다. 값이 없어진 이유까지 알아야 한다면 원래 JSONB에서 타입과 키 존재를 먼저 확인해야 한다.

같은 42로 보여도 반환 타입은 다르다

아래는 격리된 PostgreSQL 18.6에서 합성 값으로 실행한 결과다. PostgreSQL 18의 실습용 데이터베이스에 접속한 SQL 클라이언트에서 각 쿼리를 그대로 실행할 수 있다. 테이블을 만들거나 기존 데이터를 읽을 필요는 없다. 출력의 |는 열 구분자이며, 뒤에서 나오는 [SQL NULL]은 SQL NULL을 눈에 보이게 지정한 표시다. 클라이언트 기본 설정에서는 빈칸으로 보일 수 있다.

WITH samples(label, doc) AS (
  VALUES
    ('string', '{"v":"Ada"}'::jsonb),
    ('number', '{"v":42}'::jsonb),
    ('numeric-string', '{"v":"42"}'::jsonb)
)
SELECT label,
       doc->'v' AS json_value,
       doc->>'v' AS text_value,
       pg_typeof(doc->'v') AS json_sql_type,
       pg_typeof(doc->>'v') AS text_sql_type,
       jsonb_typeof(doc->'v') AS json_kind
FROM samples;
label | json_value | text_value | json_sql_type | text_sql_type | json_kind
string | "Ada" | Ada | jsonb | text | string
number | 42 | 42 | jsonb | text | number
numeric-string | "42" | 42 | jsonb | text | string

문자열 Ada는 ->에서 "Ada", ->>에서 Ada로 보였다. 두 연산자의 SQL 반환 타입은 모든 행에서 각각 jsonb, text였다. 숫자 42도 ->>로 꺼내면 SQL 숫자가 아니라 text다. PostgreSQL 18의 JSON 연산자 문서 (새 탭에서 열림)에 명시된 반환 타입과 일치한다.

pg_typeof는 SQL 값의 타입을 확인하는 함수다. jsonb_typeof는 JSONB 안에 있는 값의 종류를 알려 준다. 그래서 json_sql_type은 같아도 json_kind는 number 또는 string으로 갈린다. pg_typeof 공식 설명 (새 탭에서 열림)도 두 함수를 구별할 때 참고할 수 있다.

특히 JSON 숫자 42와 JSON 문자열 "42"는 ->>를 거치면 둘 다 같은 text 값 42가 된다. 원래 JSON 타입을 구분해야 하는 비교나 후속 JSON 처리는 -> 쪽에서 수행해야 한다.

따옴표가 보인다고 (doc->'v')::text로 바꾸는 것도 해결되지 않았다. Ada 입력에서 이 캐스트의 결과는 "Ada"였고, doc->>'v'의 결과는 Ada였다. JSON 값을 text로 표현하는 것과 문자열 내용을 추출하는 것은 다르다. say "hi"를 넣은 별도 실행에서도 ->>는 역슬래시 없는 원래 문자열 내용을 돌려줬다. 따옴표 문자를 잘라 내기보다 추출 연산자를 고른다.

일반 문자열 비교는 doc->>'v' = 'Ada'로 쓴다. JSON 값으로 비교할 때는 doc->'v' = '"Ada"'::jsonb처럼 오른쪽도 JSON 문자열로 맞춘다. Ada 입력에서 두 비교는 모두 참이었다. 반면 doc->'v' = 'Ada'는 오른쪽을 JSONB로 해석하는 과정에서 22P02, Token "Ada" is invalid. 오류로 실패했다. SQL 문자열 리터럴의 작은따옴표만으로 JSON 문자열의 큰따옴표까지 만들어지지는 않는다.

text 추출 뒤에는 JSON null과 없는 키가 같아진다

키 v에 JSON null이 있는 객체, 키가 없는 객체, JSONB 열 자체가 SQL NULL인 행을 비교했다. 문자열 "null"도 함께 넣었다. 여기서 json_sql_null은 -> 결과의 SQL NULL 여부, text_sql_null은 ->> 결과의 SQL NULL 여부다. t는 참, f는 거짓이다.

WITH samples(label, doc) AS (
  VALUES
    ('json-null', '{"v":null}'::jsonb),
    ('missing', '{}'::jsonb),
    ('sql-null', NULL::jsonb),
    ('string-null', '{"v":"null"}'::jsonb)
)
SELECT label,
       doc IS NULL AS doc_null,
       doc ? 'v' AS has_v,
       (doc->'v') IS NULL AS json_sql_null,
       (doc->>'v') IS NULL AS text_sql_null,
       jsonb_typeof(doc->'v') AS json_kind
FROM samples;
label | doc_null | has_v | json_sql_null | text_sql_null | json_kind
json-null | f | t | f | t | null
missing | f | f | t | t | [SQL NULL]
sql-null | t | [SQL NULL] | t | t | [SQL NULL]
string-null | f | t | f | f | string
  • json-null: 키는 있고 -> 결과는 JSON null 값이어서 json_sql_null은 f다. ->> 결과는 SQL NULL이어서 text_sql_null은 t다.
  • missing: 원래 객체는 있지만 키가 없다. has_v가 f이고, 두 추출 결과는 모두 SQL NULL이다.
  • sql-null: 원래 JSONB 값 자체가 SQL NULL이다. doc_null이 t이고 키 존재 검사도 SQL NULL이 된다.
  • string-null: 네 글자 문자열 null이다. JSON 타입은 string이며 두 추출 결과 모두 SQL NULL이 아니다.

json-null 행의 json_kind에 보이는 null은 SQL NULL이 아니라 함수가 돌려준 text 값 'null'이다. 별도 실행에서 이 결과의 IS NULL은 f였다. 반면 없는 키와 SQL NULL 입력의 json_kind는 실제 SQL NULL이다. 공식 문서도 jsonb_typeof의 null 결과와 SQL NULL (새 탭에서 열림)을 구별한다.

따라서 (doc->>'v') IS NULL만 검사하면 위의 앞 세 행을 모두 고르게 된다. 키 누락과 명시적인 JSON null을 다르게 처리해야 한다면 doc IS NULL, doc ? 'v', jsonb_typeof(doc->'v')를 함께 확인한다. 이 예제처럼 입력이 객체라는 전제에서 ?는 키 존재를 판별한다. 실제 입력의 구조가 다르면 그 전제부터 확인해야 한다.

숫자 계산을 하려면 text 추출 뒤에도 변환이 필요하다

->>는 숫자로 바꾸는 연산자가 아니다. 다음은 유효한 두 입력을 numeric으로 명시적으로 변환한 실행이다. ::numeric은 PostgreSQL의 타입 캐스트 문법 (새 탭에서 열림)이다.

WITH samples(label, doc) AS (
  VALUES
    ('number', '{"v":42}'::jsonb),
    ('numeric-string', '{"v":"42"}'::jsonb)
)
SELECT label,
       (doc->>'v')::numeric AS amount,
       pg_typeof((doc->>'v')::numeric) AS sql_type
FROM samples;
label | amount | sql_type
number | 42 | numeric
numeric-string | 42 | numeric

두 값 모두 변환됐지만, 숫자가 아닌 문자열을 넣으면 같은 식이 실패한다. 아래 쿼리는 의도적으로 오류를 내는 예제이므로 다른 작업의 트랜잭션과 분리해 실행한다.

SELECT ('{"v":"forty-two"}'::jsonb->>'v')::numeric;
ERROR:  22P02: invalid input syntax for type numeric: "forty-two"

두 개의 42가 변환됐다는 관찰은 모든 JSON 값의 캐스트가 안전하다는 뜻이 아니다. 실제 열에 적용하려면 JSON 숫자만 허용할지, 숫자 모양의 문자열도 허용할지, 누락과 JSON null을 어떻게 처리할지 먼저 정하고 입력을 검증해야 한다. 이 실험은 위의 값과 숫자 변환 성공 두 건·비숫자 문자열 실패 한 건을 확인했다. 모든 숫자 형식이나 범위, 배열·객체의 text 추출 동작은 검증하지 않았다.

← 전체 글 보기