[PostgreSQL] 데이터 타입이 text 이고, JSON 문자열인 열의 값 수정

PostgreSQL에서 JSON 문자열의 특정 값을 수정하는 방법

PostgreSQL에서 JSON 형태의 데이터를 문자열로 저장하고 있을 때, 특정 값을 업데이트하려면 문자열 데이터를 JSON으로 변환하여 수정한 뒤 다시 문자열로 변환해야 합니다. 아래는 JSON 문자열에서 upperLimit 또는 lowerLimit 값을 수정하는 방법을 설명합니다.

---

문제 상황

컬럼에 아래와 같은 JSON 문자열이 저장되어 있다고 가정합니다:

{
  "type": "default",
  "type": "default",
  "spec": {
    "upperLimit": 4.0,
    "lowerLimit": 0.0
  }
}

여기에서:

• upperLimit 값을 기존 값과 상관없이 새 값으로 수정하고 싶습니다.
• 또는 lowerLimit 값을 기존 값과 상관없이 새 값으로 수정하고 싶습니다.

---

해결 방법

PostgreSQL에서는 jsonb_set 함수를 사용하여 JSON 데이터를 수정할 수 있습니다. 하지만 문자열 데이터는 json이나 jsonb 타입으로 변환한 뒤 수정해야 합니다. 수정 후 다시 문자열로 변환하여 저장합니다.

---

예제 쿼리

lowerLimit 값을 수정

lowerLimit 값을 1.0으로 수정하는 쿼리는 아래와 같습니다:

UPDATE your_table
SET json_column = jsonb_set(json_column::jsonb, '{spec,lowerLimit}', '1.0'::jsonb)::text
WHERE json_column::jsonb ? 'spec';

---

upperLimit 값을 수정

upperLimit 값을 5.0으로 수정하는 쿼리는 아래와 같습니다:

UPDATE your_table
SET json_column = jsonb_set(json_column::jsonb, '{spec,upperLimit}', '5.0'::jsonb)::text
WHERE json_column::jsonb ? 'spec';

---

lowerLimit과 upperLimit을 동시에 수정

lowerLimit 값을 1.0으로, upperLimit 값을 5.0으로 동시에 수정하려면 jsonb_set 함수를 중첩 사용합니다:

UPDATE your_table
SET json_column = jsonb_set(
                    jsonb_set(json_column::jsonb, '{spec,lowerLimit}', '1.0'::jsonb),
                    '{spec,upperLimit}', '5.0'::jsonb
                 )::text
WHERE json_column::jsonb ? 'spec';

---

쿼리 설명

1. json_column::jsonb
   문자열 데이터를 jsonb 타입으로 변환합니다.
2. jsonb_set 함수
   JSON 데이터의 특정 키를 업데이트합니다.
   • 첫 번째 인자는 수정할 JSON 데이터입니다.
   • 두 번째 인자는 수정할 키의 경로 (예: '{spec,lowerLimit}').
   • 세 번째 인자는 새로운 값 ('1.0'::jsonb).
3. ::text
   수정된 JSON 데이터를 다시 문자열로 변환하여 저장합니다.
4. WHERE json_column::jsonb ? 'spec'
   JSON 데이터에서 spec 키가 존재하는 행만 업데이트합니다.

---

참고 사항

1. 데이터 타입 관리
   JSON 데이터를 문자열로 저장하지 말고, 가능하면 jsonb 타입 컬럼으로 변환하여 사용하는 것을 권장합니다. 이를 위해 아래와 같이 컬럼을 변경할 수 있습니다:
2. ALTER TABLE your_table ALTER COLUMN json_column TYPE jsonb USING json_column::jsonb;
3. 유효성 검사
   JSON 문자열에 유효하지 않은 값이 포함된 경우 변환 중 오류가 발생할 수 있으므로, 업데이트 전에 데이터의 유효성을 확인해야 합니다.
4. 성능 최적화
   jsonb 타입은 JSON 데이터를 효율적으로 처리하도록 설계되었습니다. 문자열 데이터를 변환하는 작업은 비용이 크므로, 데이터가 크거나 자주 업데이트가 필요한 경우 성능에 주의하세요.