Supabase 쓰다가 다국어 지원에 Jsonb 컬럼을 쓰게 되어서 정리해두는 글
예제 데이터
CREATE TABLE products (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
name_i18n JSONB,
metadata JSONB,
price NUMERIC
);
INSERT INTO products (id, name, name_i18n, metadata, price) VALUES
(
'PRD-001',
'Laptop',
'{
"ko": "노트북",
"en": "Laptop",
"ja": "ノートパソコン"
}',
'{
"brand": "Acme",
"tags": ["electronics", "office"],
"spec": {
"cpu": "M3",
"ram": "16GB"
}
}',
1299
);
-> 연산자와 ->> 연산자
SELECT name_i18n->'ko' FROM products;
-- "노트북"
SELECT name_i18n->>'ko' FROM products;
-- 노트북
결과를 보면 -> 연산자는 따옴표를 포함한 값을 리턴하고, ->> 연산자는 텍스트 값만 리턴한다.
-> 연산자는 Jsonb 타입을 그대로 리턴하기 때문에 Json 구조를 그대로 다룰 때 사용하고,
->> 연산자는 텍스트 타입을 반환한다.
->> 연산자를 이렇게 활용해볼 수 있다.
SELECT
id,
name_i18n->>'ko' AS name_ko,
name_i18n->>'en' AS name_en,
name_i18n->>'ja' AS name_ja
FROM products;
-- id | name_ko | name_en | name_ja
-- ---------+---------+----------+----------------
-- PRD-001 | 노트북 | Laptop | ノートパソコン
-- (1 row)
name_i18n 컬럼에서 각 키에 해당하는 텍스트만 뽑아서 새롭게 리턴할 수 있다.
그럼 ->, ->> 둘 중에 하나만 존재하면 되지,
왜 굳이 둘 다 존재하는가?
SELECT metadata->'spec' AS spec_json
FROM products;
-- spec_json
-- ------------------------------
-- {"cpu": "M3", "ram": "16GB"}
-- (1 row)
SELECT metadata->>'spec' AS spec_text
FROM products;
-- spec_text
-- ------------------------------
-- {"cpu": "M3", "ram": "16GB"}
-- (1 row)
위 예시에서 ->와 ->> 두가지 연산을 실행했을 때 결과 값이 {"cpu": "M3", "ram": "16GB"}로 동일하게 나온다. 얼핏 봐서는 동일한 역할을 수행하는 것처럼 보이는데, 다음 연산이 또 존재할 때는 이야기가 달라진다. 왜냐하면 첫번째 쿼리의 결과물은 타입이 JSON이지만, 두번째 쿼리의 결과물은 TEXT이기 때문이다.
SELECT metadata->'spec'->>'cpu' AS cpu_text
FROM products;
-- cpu_text
-- ----------
-- M3
-- (1 row)
SELECT metadata->>'spec'->>'cpu' AS cpu_text
FROM products;
-- psql:commands.sql: ERROR: operator does not exist: text ->> unknown
-- LINE 1: SELECT metadata->>'spec'->>'cpu' AS cpu_text
spec 키의 값인 {"cpu": "M3", "ram": "16GB"} 중에 cpu 키에 접근하고 싶다고 한다면, 위 예시처럼 할 수 있다.
첫번째 쿼리는 metadata 컬럼의 spec에 jsonb 형태로 접근해서 cpu 키에 해당하는 값을 텍스트로 뽑아달라고 했으니 M3가 결과로 나온걸 확인할 수 있다.
반면에 두번째 쿼리는 metadata 컬럼의 spec에 텍스트 형태로 접근했기 때문에 PostgreSQL 입장에서 text 데이터인 {"cpu": "M3", "ram": "16GB"}에서 key 가 cpu인 것을 찾을 수가 없다.
SELECT (metadata->>'spec')::jsonb->>'cpu' AS cpu_text
FROM products;
-- cpu_text
-- ----------
-- M3
-- (1 row)
따라서 첫번째 단계에서 ->> 연산자를 이용해서 metadata에 대한 결과값을 텍스트 형태로 받아왔다면 위 예시처럼 ::jsonb를 활용해서 결과값을 다시 jsonb 형태로 만든 다음에서야 cpu라는 키 값에 접근할 수 있게 된다.
#> 연산자와 #>> 연산자
SELECT metadata#>'{spec,cpu}' FROM products;
-- "M3"
SELECT metadata#>>'{spec,cpu}' FROM products;
-- M3
#> 연산자와 #>> 연산자 중첩 경로에 있는 값에 접근할 때 사용한다. 예시 데이터 중 metadata 컬럼 값을 보면 spec 키 값 안에 cpu 키가 또 존재하는 것을 확인할 수 있다.
결과를 보면 #> 연산자는 따옴표를 포함해서 Jsonb 타입 그대로를 반환하고 있는걸 볼 수 있다.
#>> 연산자는 값을 따옴표 없이 텍스트만 반환하는 걸 볼 수 있다.
@> 연산자
@> 연산자는 Jsonb가 특정 구조를 포함하는지 확인할 때 사용한다.
첫번째 쿼리는 {"brand": "Acme"} 구조가 있는지를 확인하는 것이기 때문에 1행이 리턴되었고,
두번째 쿼리는 데이터에 없는 {"brand": "A"} 구조가 있는지를 요청했기 때문에 0행이 리턴되었다.
SELECT *
FROM products
WHERE metadata @> '{"brand": "Acme"}';
-- id | name | name_i18n | metadata | price
-- ---------+----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------+-------
-- PRD-001 | Laptop | {"en": "Laptop", "ja": "ノートパソコン", "ko": "노트북"} | {"spec": {"cpu": "M3", "ram": "16GB"}, "tags": ["electronics", "office"], "brand": "Acme"} | 1299
-- (1 row)
SELECT *
FROM products
WHERE metadata @> '{"brand": "A"}';
-- id | name | name_i18n | metadata | price
-- ----+------+-----------+----------+-------
-- (0 rows)
? 연산자
? 연산자는 키 존재 여부를 확인할 때 사용한다.
첫번째 쿼리는 name_i18n 컬럼에 'ko' 키가 존재하는지를 확인하는 것이기 때문에 1행이 리턴되었고,
두번째 쿼리는 name_i18n 컬럼에 존재하지 않는 'zh' 키가 있는지를 확인하는 것이기 때문에 0행이 리턴되었다.
SELECT *
FROM products
WHERE name_i18n ? 'ko';
-- id | name | name_i18n | metadata | price
-- ---------+----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------+-------
-- PRD-001 | Laptop | {"en": "Laptop", "ja": "ノートパソコン", "ko": "노트북"} | {"spec": {"cpu": "M3", "ram": "16GB"}, "tags": ["electronics", "office"], "brand": "Acme"} | 1299
-- (1 row)
SELECT *
FROM products
WHERE name_i18n ? 'zh';
-- id | name | name_i18n | metadata | price
-- ----+------+-----------+----------+-------
-- (0 rows)
?& 연산자
?& 연산자는 모든 키가 존재하는지 확인할 때 사용한다.
첫번째 쿼리는 name_i18n 컬럼에 ko, en 키 값이 모두 존재하기 때문에 1행이 리턴되었고,
두번째 쿼리는 name_i18n 컬럼에 ko는 존재하나 zh는 존재하지 않기 때문에 0행이 리턴되었다.
SELECT *
FROM products
WHERE name_i18n ?& array['ko', 'en'];
-- id | name | name_i18n | metadata | price
-- ---------+----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------+-------
-- PRD-001 | Laptop | {"en": "Laptop", "ja": "ノートパソコン", "ko": "노트북"} | {"spec": {"cpu": "M3", "ram": "16GB"}, "tags": ["electronics", "office"], "brand": "Acme"} | 1299
-- (1 row)
SELECT *
FROM products
WHERE name_i18n ?& array['ko', 'zh'];
-- id | name | name_i18n | metadata | price
-- ----+------+-----------+----------+-------
-- (0 rows)
?| 연산자
?| 연산자는 값이 하나라도 존재하는지 확인할 때 사용한다.
첫번째 쿼리는 name_i18n 컬럼에 zh 키는 존재하지 않으나 ko가 존재하기 때문에 1행이 리턴되었고,
두번째 쿼리는 name_i18n 컬럼에 fr, zh 키 모두 존재하지 않기 때문에 0행이 리턴되었다.
SELECT *
FROM products
WHERE name_i18n ?| array['ko', 'zh'];
-- id | name | name_i18n | metadata | price
-- ---------+----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------+-------
-- PRD-001 | Laptop | {"en": "Laptop", "ja": "ノートパソコン", "ko": "노트북"} | {"spec": {"cpu": "M3", "ram": "16GB"}, "tags": ["electronics", "office"], "brand": "Acme"} | 1299
-- (1 row)
SELECT *
FROM products
WHERE name_i18n ?| array['fr', 'zh'];
-- id | name | name_i18n | metadata | price
-- ----+------+-----------+----------+-------
-- (0 rows)
Jsonb를 활용한 다국어 적용 예제
COALESCE
SELECT
COALESCE(
name_i18n->>'ko',
name_i18n->>'en',
name
) AS display_name
FROM products;
-- display_name
-- --------------
-- 노트북
-- (1 row)
SELECT
COALESCE(
name_i18n->>'zh',
name_i18n->>'en',
name
) AS display_name
FROM products;
-- display_name
-- --------------
-- Laptop
-- (1 row)
COALESCE(a, b, c)의 경우에는 왼쪽부터 순서대로 null이 아닌 첫 값을 리턴한다.
그래서 첫번째 쿼리는 name_i18n에 ko 키가 있기 때문에 노트북이 리턴되었고,
두번째 쿼리는 name_i18n에 zh 키가 없기 때문에 그 다음에 존재하는 en키 값에 해당하는 값인 Laptop이 리턴된다.
CREATE FUNCTION
CREATE FUNCTION get_localized_text( -- 함수명 선언
default_text TEXT, -- 파라미터 1
translations JSONB, -- 파라미터 2
lang TEXT -- 파라미터 3
)
RETURNS TEXT -- 리턴 값 타입 명시
AS $$ -- BODY 시작임을 알림
SELECT COALESCE( -- 로직 본문
translations->>lang,
translations->>'en',
default_text
);
-- 위 함수 선언을 C 언어 스타일로 바꿔보면 이런 느낌이다.
TEXT get_localized_text(TEXT default_text, JSONB translations, TEXT lang) {
SELECT COALESCE(
translations->>lang,
translations->>'en',
default_text
);
}
다국어 지원 예시
예제로 Jsonb 컬럼으로 다국어 지원을 제공하는 쿼리를 작성해봤다.
ko, en, jp의 경우 해당하는 언어로 제품명을 출력하되, 만약 jsonb 컬럼에 존재하지 않는 언어가 요청된다면 영어로 출력하는 예제이다.
CREATE FUNCTION get_localized_text(
default_text TEXT,
translations JSONB,
lang TEXT
)
RETURNS TEXT
AS $$
SELECT COALESCE(
translations->>lang,
translations->>'en',
default_text
);
SELECT
id,
get_localized_text(name, name_i18n, 'ko') AS name
FROM products;
-- display_name
-- --------------
-- 노트북
-- (1 row)
SELECT
id,
get_localized_text(name, name_i18n, 'zh') AS name
FROM products;
-- display_name
-- --------------
-- Laptop
-- (1 row)
인풋으로 받은 언어에 해당하는 제품명을 출력하는 get_localized_text라는 함수를 만들고, 이렇게 만든 함수를 SELECT 문을 사용할 때 같이 쓰면 된다.
첫번째 쿼리는 ko를 요청하고 있기 때문에 한국어 제품명인 노트북을 리턴하고 있고,
두번째 쿼리는 데이터에 없는 zh(중국어) 데이터를 요청하고 있기 때문에 COALESCE에서 두번째로 대응되는 영어(en) 제품명인 Laptop을 리턴하게 된다.
추가로, 만약에 en 키에 해당하는 값 마저 없다면 get_localized_text 함수의 첫번째 파라미터인 name 컬럼의 값 Laptop이 출력된다.
PostgreSQL Playground: https://onecompiler.com/postgresql
* 이 플레이그라운드는 한 번 실행하면 다음 실행에서는 이전 기록을 가지고 있지 않으니까 실행할 때마다 create table, insert문을 함께 실행시켜줘야 한다.
PostgreSQL Online Editor
onecompiler.com
