[SQL] PostgreSQL JSON columns

MySQL, PostgreSQL은 JSON 형태로 데이터를 칼럼에 저장하고 다룰 수(검색, 수정 등) 있다 

ex) 배달앱: 다양한 메뉴에 각기 다른 옵션이 존재. 데이터가 어떤 형태인지 알 수 없다(SQL 테이블을 만들기 어렵다) 

 

JSON: 텍스트 그대로 저장 

JSONB: 바이너리 형식으로 저장 

CREATE TABLE users (
  user_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 
  -- GENERATED ALWAYS AS IDENTITY: Auto-increment 
  profile JSONB
  );


INSERT INTO users (profile) VALUES
('{"name": "Taco", "age": 30, "city": "Budapest"}'),
('{"name": "Giga", "age": 25, "city": "Tbilisi", "hobbies": ["reading", "climbing"]}');
-- JSON: 유연하다(데이터를 추가할 수 있다) 


INSERT INTO users (profile) VALUES 
(json_build_object('name', 'Taco', 'age', 30, 'city', 'Budapest')),
(json_build_object('name', 'Giga', 'age', 25, 'city', 'Tbilisi', 
'hobbies', json_build_array('reading', 'climbing')));
-- 데이터를 넣는 더 좋은 방식 


SELECT 
  profile ->> 'name' AS name,
  profile ->> 'city' AS city,
  profile ->> 'age' AS age,
  profile -> 'hobbies' AS hobby
  FROM users;
-- 칼럼 형태로 조회하기 


SELECT 
  profile ->> 'name' AS name,
  profile ->> 'city' AS city,
  profile ->> 'age' AS age,
  profile -> 'hobbies' -> 0 AS first_hobby
  -- ->> : 결과값의 ""가 제거
  -- 'hobbies' -> 0: 'hobbies' 중 첫번째 값만 반환 
  FROM users
  WHERE profile ? 'hobbies';
  -- profile ? 'hobbies': 'hobbies'의 값이 있는 데이터만 반환 
  -- profile -> 'hobbies' ? 'climbing': 'hobbies' 중 'climbing'값이 있는 데이터만 반환 
  

-- 기존 'hobbies' 배열에 'cooking'을 추가
UPDATE users
  SET profile = profile || jsonb_set(
    profile, 
    '{hobbies}', 
    (profile -> 'hobbies') || jsonb_build_array('cooking')
  )
  WHERE profile ? 'hobbies';  -- hobbies 키가 있는 행만 업데이트

 

 

더 많은 연산자 확인 👇

https://www.postgresql.org/docs/9.5/functions-json.html