재우니의 블로그

MSSQL 2016 에서 JSON 데이터 복합 수정 작업

MSSQL에서 JSON_MODIFY() 함수는 JSON 데이터의 특정 값을 수정할 때 매우 유용합니다. 하지만 JSON 데이터 내 여러 값을 동시에 수정해야 하는 경우, 한 번에 수정할 수 없으므로 여러 번의 JSON_MODIFY() 호출이 필요합니다. 이번 글에서는 복합 작업을 수행하는 다양한 예시를 통해 JSON 데이터를 효율적으로 수정하는 방법을 설명하겠습니다.

1. 기본적인 JSON 수정

예제 1: 단일 값 수정

먼저, 단일 값을 수정하는 간단한 예제부터 살펴보겠습니다.

DECLARE @jsonInfo NVARCHAR(MAX) 
SET @jsonInfo = N'{
    "info": {
        "type": 1,
        "address": {
            "town": "Bristol",
            "county": "Avon",
            "country": "England"
        },
        "tags": ["Sport", "Water polo"]
    },
    "type": "Basic"
}'

-- type 값을 "Basic"에서 "Special"로 수정
SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.type', 'Special');
PRINT @jsonInfo;

결과:

{
    "info": {
        "type": 1,
        "address": {
            "town": "Bristol",
            "county": "Avon",
            "country": "England"
        },
        "tags": ["Sport", "Water polo"]
    },
    "type": "Special"
}

2. 여러 값을 동시에 수정하기

여러 값의 수정이 필요할 때는 JSON_MODIFY() 함수를 여러 번 중첩하여 사용해야 합니다. 다음 예시에서는 세 가지 값을 연속으로 수정합니다.

예제 2: 복합 수정 작업

SET @jsonInfo = JSON_MODIFY(
    JSON_MODIFY(
        JSON_MODIFY(@jsonInfo, '$.type', 'Special'),
    '$.info.address.town', 'Seoul'),
'$.info.type', 2);

설명:

  1. 첫 번째 JSON_MODIFY()type 값을 "Basic"에서 "Special"로 수정합니다.
  2. 두 번째 JSON_MODIFY()town 값을 "Bristol"에서 "Seoul"로 변경합니다.
  3. 세 번째 JSON_MODIFY()info.type 값을 1에서 2로 변경합니다.

결과:

{
    "info": {
        "type": 2,
        "address": {
            "town": "Seoul",
            "county": "Avon",
            "country": "England"
        },
        "tags": ["Sport", "Water polo"]
    },
    "type": "Special"
}

3. 다중 수정 작업 예시

예제 3: 다중 속성 수정

JSON_MODIFY()를 사용하여 여러 속성을 동시에 수정하는 방법입니다. 복잡한 JSON 구조 내에서 다양한 필드를 수정할 때 활용할 수 있습니다.

SET @jsonInfo = JSON_MODIFY(
    JSON_MODIFY(
        JSON_MODIFY(@jsonInfo, '$.info.address.county', 'Seoul'),
    '$.info.tags[1]', 'Tennis'),
'$.type', 'Premium');

설명:

  1. info.address.county 값을 "Avon"에서 "Seoul"로 수정합니다.
  2. tags[1] 값을 "Water polo"에서 "Tennis"로 변경합니다.
  3. 최종적으로 type 값을 "Special"에서 "Premium"으로 변경합니다.

결과:

{
    "info": {
        "type": 2,
        "address": {
            "town": "Seoul",
            "county": "Seoul",
            "country": "England"
        },
        "tags": ["Sport", "Tennis"]
    },
    "type": "Premium"
}

4. 속성 이름 변경하기

MSSQL에서는 기존 속성의 값을 새로운 속성에 복사하고, 원래 속성을 삭제하여 속성 이름을 변경할 수 있습니다.

예제 4: 속성 이름 변경하기

SET @jsonInfo = JSON_MODIFY(
    JSON_MODIFY(@jsonInfo, '$.Type', JSON_VALUE(@jsonInfo, '$.type')),
'$.type', NULL);

설명:

  1. JSON_VALUE()를 사용해 $.type 값을 추출하여 새로운 속성 $.Type에 저장합니다.
  2. 기존 $.type 값은 NULL로 설정하여 삭제합니다.

결과:

{
    "info": {
        "type": 2,
        "address": {
            "town": "Seoul",
            "county": "Seoul",
            "country": "England"
        },
        "tags": ["Sport", "Tennis"]
    },
    "Type": "Premium"
}

5. 값 증분하기

JSON_VALUE()JSON_MODIFY()를 결합하여 숫자 값을 증분할 수도 있습니다.

예제 5: 값 증분하기

SET @jsonInfo = JSON_MODIFY(
    @jsonInfo, '$.info.type', CAST(JSON_VALUE(@jsonInfo, '$.info.type') AS INT) + 1);

설명:

  • JSON_VALUE()를 사용하여 현재 $.info.type 값을 가져온 후 +1을 수행하여 수정합니다.

결과:

{
    "info": {
        "type": 3,
        "address": {
            "town": "Seoul",
            "county": "Seoul",
            "country": "England"
        },
        "tags": ["Sport", "Tennis"]
    },
    "Type": "Premium"
}

6. 배열 수정

배열 값을 업데이트할 때도 JSON_MODIFY()JSON_QUERY()를 함께 사용할 수 있습니다.

예제 6: 배열 값 수정하기

SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.info.tags', JSON_QUERY('["game", "watch"]'));

설명:

  • JSON_QUERY()를 사용하여 배열 형식의 값을 수정합니다. 이 때 JSON_MODIFY()를 사용할 때는 자동 이스케이프가 붙을 수 있으니 주의가 필요합니다.

결과:

{
    "info": {
        "type": 3,
        "address": {
            "town": "Seoul",
            "county": "Seoul",
            "country": "England"
        },
        "tags": ["game", "watch"]
    },
    "Type": "Premium"
}

결론

MSSQL의 JSON_MODIFY() 함수는 JSON 데이터를 유연하게 수정할 수 있는 강력한 도구입니다. 여러 값을 동시에 수정하려면 JSON_MODIFY()를 여러 번 호출하여 중첩된 JSON 구조 내에서 원하는 값을 업데이트할 수 있습니다. 복합 수정 작업을 통해 JSON 데이터를 효율적으로 처리하고 유지보수할 수 있습니다.

참고 자료:


복합 작업에 대한 원리

MSSQL의 JSON_MODIFY() 함수는 JSON 데이터에서 값을 수정할 때 매우 유용하지만, 한 번에 여러 값을 수정할 수는 없습니다. 각 호출에서 하나의 경로에 대한 값만 수정할 수 있기 때문에, 여러 값을 수정하려면 JSON_MODIFY()를 여러 번 중첩해야 합니다. 이를 쉽게 이해할 수 있도록 단계별로 설명하겠습니다.

1. JSON_MODIFY() 기본 개념

JSON_MODIFY()는 JSON 데이터의 하나의 값을 수정하고, 그 결과 수정된 JSON 문자열을 반환합니다. 이 함수는 한 번 호출에 한 경로만 수정할 수 있습니다. 즉, JSON 내에서 여러 값을 수정하려면 그만큼 JSON_MODIFY()를 여러 번 호출해야 합니다.

예시:

SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.info.address.county', 'Seoul');

여기서는 JSON 데이터에서 $.info.address.county 값을 "Seoul"로 수정합니다. 하지만 만약 다른 값을 동시에 수정하고 싶다면, 또 다른 JSON_MODIFY()를 사용해야 합니다.

2. JSON_MODIFY()를 중첩하는 이유

만약 여러 값을 한 번에 수정하고자 한다면, JSON_MODIFY()를 중첩하여 사용할 수 있습니다. 이때 중첩의 원리는 수정된 JSON을 다시 사용하는 것입니다. 첫 번째 JSON_MODIFY()가 수정된 JSON을 반환하면, 그 결과를 다시 두 번째 JSON_MODIFY()의 입력값으로 사용하게 됩니다.

3단계 예시:

SET @jsonInfo = JSON_MODIFY(
    JSON_MODIFY(
        JSON_MODIFY(@jsonInfo, '$.info.address.county', 'Seoul'),
    '$.info.type', 2),
'$.type', 'Premium');

단계별로 살펴보면:

  1. 첫 번째 JSON_MODIFY(): $.info.address.county의 값을 "Seoul"로 변경합니다.
    • 수정된 JSON은 다음과 같습니다:
    • { "info": { "type": 1, "address": { "town": "Bristol", "county": "Seoul", "country": "England" }, "tags": ["Sport", "Water polo"] }, "type": "Basic" }
  2. 두 번째 JSON_MODIFY(): 첫 번째 수정된 JSON을 받아, $.info.type 값을 1에서 2로 변경합니다.
    • 이 결과로 JSON은 다음과 같이 변경됩니다:
    • { "info": { "type": 2, "address": { "town": "Bristol", "county": "Seoul", "country": "England" }, "tags": ["Sport", "Water polo"] }, "type": "Basic" }
  3. 세 번째 JSON_MODIFY(): 마지막으로 $.type 값을 "Basic"에서 "Premium"으로 변경합니다.
    • 최종 결과는:
    • { "info": { "type": 2, "address": { "town": "Bristol", "county": "Seoul", "country": "England" }, "tags": ["Sport", "Water polo"] }, "type": "Premium" }

3. 왜 3단계일까?

이 방법은 각 단계에서 수정된 JSON을 다시 입력값으로 사용하는 방식입니다. 한 번에 하나의 값을 수정해야 하므로, 수정하고 싶은 값이 여러 개일 때 JSON_MODIFY()를 계속 중첩하여 적용합니다. 중첩의 순서는 상관없지만, 수정하고자 하는 값들을 단계별로 나누어 처리하는 것이 원리입니다.

4. 한 번에 여러 값을 수정하는 방법이 없는 이유

MSSQL에서는 한 번에 여러 경로의 값을 수정할 수 있는 함수가 존재하지 않기 때문에, 여러 값을 수정하려면 각각의 경로를 따로 수정하는 방식으로 JSON_MODIFY()를 중첩해야 합니다. 이런 방식은 SQL 자체가 상태 기반이기 때문에, 하나의 호출이 완료된 후에야 다음 호출이 그 결과를 받을 수 있습니다.


결론

JSON_MODIFY()를 중첩해서 사용하는 이유는 JSON의 여러 값을 한 번에 수정할 수 없기 때문입니다. 각 JSON_MODIFY() 호출은 JSON 객체의 특정 값을 수정하고, 그 결과를 다음 단계로 넘기게 됩니다. 주니어 개발자라면 이 원리를 이해하고, 여러 값을 수정할 때 중첩 호출이 필요하다는 점을 기억해 주세요.

: JSON 데이터를 처리할 때 경로를 정확히 지정하고, 원하는 값이 올바르게 수정되었는지 단계별로 확인해보는 것이 중요합니다.

 


만약에 10개의 항목을 수정하고자 한다면, 어떻게 중첩 JSON_MODIFY 를 사용하는게 좋은가?

MSSQL에서는 JSON_MODIFY() 함수로 한 번에 하나의 값만 수정할 수 있습니다. 따라서, 10개의 항목을 수정해야 할 때는 여러 번 JSON_MODIFY()를 호출해야 합니다. 이를 수행하는 방법으로는 두 가지 주요 옵션이 있습니다:

1. 중첩된 JSON_MODIFY() 사용

중첩 구조를 사용하여 여러 값을 한 번에 처리할 수 있습니다. 각 호출이 이전 호출의 결과를 인자로 받아 다음 값을 수정하는 방식입니다.

예시:

SET @jsonInfo = JSON_MODIFY(
    JSON_MODIFY(
        JSON_MODIFY(
            JSON_MODIFY(
                JSON_MODIFY(
                    JSON_MODIFY(
                        JSON_MODIFY(
                            JSON_MODIFY(
                                JSON_MODIFY(
                                    JSON_MODIFY(@jsonInfo, '$.path1', 'value1'),
                                '$.path2', 'value2'),
                            '$.path3', 'value3'),
                        '$.path4', 'value4'),
                    '$.path5', 'value5'),
                '$.path6', 'value6'),
            '$.path7', 'value7'),
        '$.path8', 'value8'),
    '$.path9', 'value9'),
'$.path10', 'value10');

 

이처럼 10개의 값을 수정하려면 JSON_MODIFY()를 10번 중첩해서 호출해야 합니다.

2. 순차적인 SET 명령 사용

대안으로 중첩하지 않고 순차적으로 각 값을 수정할 수 있습니다. SET 명령을 여러 번 사용하여 차례로 값을 수정하는 방법입니다.

예시:

SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.path1', 'value1');
SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.path2', 'value2');
SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.path3', 'value3');
SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.path4', 'value4');
SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.path5', 'value5');
SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.path6', 'value6');
SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.path7', 'value7');
SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.path8', 'value8');
SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.path9', 'value9');
SET @jsonInfo = JSON_MODIFY(@jsonInfo, '$.path10', 'value10');

이 방법은 중첩 구조보다 가독성이 좋고, 디버깅도 용이합니다.

중첩 vs 순차적 접근

  • 중첩 사용: 중첩된 방식은 모든 수정 작업을 한 번에 처리하지만, 코드를 읽고 관리하기 어려워질 수 있습니다. 또한, 중첩이 많아지면 가독성이 매우 떨어집니다.
  • 순차적 수정: 순차적으로 SET을 사용하는 방식은 더 직관적이며, 각 단계에서 수정된 결과를 확인하기 쉽습니다. 또한, 수정하는 값이 많아질수록 중첩보다 이 방식이 더 유리합니다.

결론

10개 이상의 항목을 수정할 때, 중첩된 JSON_MODIFY()를 사용할 수 있지만, 코드의 가독성과 유지보수성을 고려한다면 순차적으로 SET을 사용하여 각 값을 수정하는 것이 더 바람직합니다. 중첩 방식은 수정할 항목이 적을 때 유용할 수 있지만, 많은 값을 수정할 때는 순차적인 방법이 더 직관적이고 관리하기 쉽습니다.

 


특정 TABLE 에 UPDATE 구문을 적용하고자 한다면?

 

아래는 JsonTest 테이블의 JsonData 필드에 저장된 JSON 데이터를 한 번에 10개 항목을 갱신하는 UPDATE 구문 예시입니다. 순차적으로 JSON_MODIFY()를 사용하여 각 항목을 갱신하고 있습니다.

예시: 10개 항목 수정

UPDATE JsonTest
SET JsonData = JSON_MODIFY(JsonData, '$.info.address.city', 'Seoul'),
    JsonData = JSON_MODIFY(JsonData, '$.info.address.state', 'Seoul State'),
    JsonData = JSON_MODIFY(JsonData, '$.info.address.zipcode', '12345'),
    JsonData = JSON_MODIFY(JsonData, '$.info.type', 'Premium'),
    JsonData = JSON_MODIFY(JsonData, '$.info.tags[0]', 'Updated Tag 1'),
    JsonData = JSON_MODIFY(JsonData, '$.info.tags[1]', 'Updated Tag 2'),
    JsonData = JSON_MODIFY(JsonData, '$.employee.firstName', 'John'),
    JsonData = JSON_MODIFY(JsonData, '$.employee.lastName', 'Doe'),
    JsonData = JSON_MODIFY(JsonData, '$.employee.title', 'Manager'),
    JsonData = JSON_MODIFY(JsonData, '$.employee.department', 'HR')
WHERE Id = 1;

설명:

  • 첫 번째 수정: $.info.address.city를 "Seoul"로 변경
  • 두 번째 수정: $.info.address.state를 "Seoul State"로 변경
  • 세 번째 수정: $.info.address.zipcode를 "12345"로 변경
  • 네 번째 수정: $.info.type을 "Premium"으로 변경
  • 다섯 번째와 여섯 번째 수정: tags 배열의 첫 번째, 두 번째 항목을 각각 "Updated Tag 1", "Updated Tag 2"로 변경
  • 나머지 항목: employee 객체의 firstName, lastName, title, department 값을 각각 수정

이 구문은 JSON 데이터의 10개 항목을 순차적으로 갱신하는 방식으로 작성되었습니다.

주의 사항:

  • JSON_MODIFY() 함수는 순차적으로 적용됩니다. 즉, 한 번 수정된 JSON 값이 다음 JSON_MODIFY() 호출에 입력으로 사용됩니다.
  • WHERE Id = 1은 수정할 행을 지정하는 조건입니다. 필요에 따라 다른 조건으로 수정할 수 있습니다.

결과:

Id가 1인 행의 JSON 데이터는 10개의 필드가 수정된 새로운 JSON으로 갱신됩니다.


이 방식은 수정할 항목이 많을 때 적합하며, 각 JSON_MODIFY() 호출이 개별적으로 수행되어 가독성을 높이고, 디버깅할 때도 용이합니다.