JSON_MODIFY(Transact-SQL)

적용 대상: SQL Server 2016 (13.x) 이상 Azure SQL 데이터베이스 Azure SQL Managed Instance Azure Synapse Analytics

JSON 문자열의 속성 값을 업데이트하고 업데이트된 JSON 문자열을 반환합니다.

Transact-SQL 구문 표기 규칙

구문

JSON_MODIFY ( expression , path , newValue )

인수

expression

식입니다. 일반적으로 JSON 텍스트를 포함하는 변수 또는 열의 이름입니다.

JSON_MODIFY은(는) 식에 유효한 JSON이 포함되지 않은 경우 오류를 반환합니다.

path

업데이트할 속성을 지정하는 JSON 경로 식입니다.

path에는 다음 구문이 있습니다.

[append] [ lax | strict ] $.<json path>

• append

  에서 참조 <json path>하는 배열에 새 값을 추가하도록 지정하는 선택적 한정자입니다.

• lax

  참조 <json path> 하는 속성이 존재하지 않도록 지정합니다. 속성이 없 JSON_MODIFY 으면 지정된 경로에 새 값을 삽입하려고 합니다. 속성을 경로에 삽입할 수 없는 경우 삽입이 실패할 수 있습니다. lax 또는 strict를 지정하지 않으면 lax가 기본 모드입니다.

• strict

  <json path>가 참조하는 속성이 JSON 식에 있어야 한다고 지정합니다. 속성이 없 JSON_MODIFY 으면 오류를 반환합니다.

• <json path>

  업데이트할 속성에 대한 경로를 지정합니다. 자세한 내용은 JSON 경로 식(SQL Server)을 참조하세요.

  SQL Server 2017(14.x) 및 Azure SQL Database에서 path 값으로 변수를 제공할 수 있습니다.

  경로의 형식이 유효하지 않은 경우 JSON_MODIFY이(가) 오류를 반환합니다.

newValue

path로 지정된 속성의 새 값입니다.

새 값은 varchar, nvarchar 또는 text여야 합니다.

lax 모드에서 JSON_MODIFY은(는) 새 값이 NULL일 경우 지정된 키를 삭제합니다.

JSON_MODIFY값의 형식이 varchar 또는 nvarchar인 경우 새 값의 모든 특수 문자를 이스케이프합니다. 텍스트 값이 제대로 형식이 지정된 JSON이 생성되는 경우 이JSON_QUERYJSON_MODIFY스케이프되지 FOR JSON않습니다.

반환 값

올바른 형식의 JSON 텍스트로 expression의 업데이트된 값을 반환합니다.

설명

이 JSON_MODIFY 함수를 사용하면 기존 속성의 값을 업데이트하거나, 새 키:값 쌍을 삽입하거나, 모드와 제공된 값의 조합에 따라 키를 삭제할 수 있습니다.

다음 표에서는 lax 모드 및 strict 모드에서 JSON_MODIFY의 동작을 비교합니다. 선택적 경로 모드 사양(lax 또는 strict)에 대한 자세한 내용은 JSON 경로 식(SQL Server)을 참조하세요.

새 값	경로가 존재	lax 모드	strict 모드
NOT NULL	예	기존 값을 업데이트합니다.	기존 값을 업데이트합니다.
NOT NULL	아니요	지정된 경로에 새 키-값 쌍을 만듭니다. 실패할 수 있습니다. 예를 들어 경로를 $.user.setting.themeJSON_MODIFY 지정하는 경우 또는 개체가 없거나 설정이 배열 또는 스칼라 값인 경우 키를 theme $.user $.user.settings 삽입하지 않습니다.	오류 – INVALID_PROPERTY
NULL	예	기존 속성을 삭제합니다.	기존 값을 null로 설정합니다.
NULL	아니요	동작이 없습니다. 첫 번째 인수가 결과로 반환됩니다.	오류 – INVALID_PROPERTY

lax 모드에서 JSON_MODIFY은(는) 새 키:값 쌍을 만들려고 시도하지만 일부 경우에는 실패할 수 있습니다.

JSON 함수는 JSON 문서가 varchar, nvarchar로 저장되든 네이티브 json 데이터 형식으로 저장되든 동일하게 작동합니다.

예제

A. 기본 작업

다음 예제에서는 JSON 텍스트를 사용하여 수행할 수 있는 기본 작업을 보여줍니다.

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Update name
SET @info = JSON_MODIFY(@info, '$.name', 'Mike');
PRINT @info;

-- Insert surname
SET @info = JSON_MODIFY(@info, '$.surname', 'Smith');
PRINT @info;

-- Set name NULL
SET @info = JSON_MODIFY(@info, 'strict $.name', NULL);
PRINT @info;

-- Delete name
SET @info = JSON_MODIFY(@info, '$.name', NULL);
PRINT @info;

-- Add skill
SET @info = JSON_MODIFY(@info, 'append $.skills', 'Azure');
PRINT @info;

결과 집합은 다음과 같습니다.

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

B. 여러 업데이트

를 사용하면 JSON_MODIFY하나의 속성만 업데이트할 수 있습니다. 여러 업데이트를 수행해야 하는 경우 여러 JSON_MODIFY 호출을 사용할 수 있습니다.

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Multiple updates
SET @info = JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(@info, '$.name', 'Mike'), '$.surname', 'Smith'), 'append $.skills', 'Azure');
PRINT @info;

결과 집합은 다음과 같습니다.

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

C. 키 이름 바꾸기

다음 예제에서는 함수를 사용하여 JSON 텍스트 JSON_MODIFY 의 속성 이름을 바꾸는 방법을 보여줍니다. 먼저 기존 속성의 값을 가져와 새 키:값 쌍으로 삽입할 수 있습니다. 그런 다음 이전 속성의 값을 .로 설정하여 이전 키를 삭제할 NULL수 있습니다.

DECLARE @product NVARCHAR(100) = '{"price":49.99}';
PRINT @product;

-- Rename property
SET @product = JSON_MODIFY(JSON_MODIFY(@product, '$.Price', CAST(JSON_VALUE(@product, '$.price') AS NUMERIC(4, 2))), '$.price', NULL);
PRINT @product;

결과 집합은 다음과 같습니다.

{
    "price": 49.99
} {
    "Price": 49.99
}

새 값을 숫자 형식으로 캐스팅하지 않는 경우 JSON_MODIFY이(가) 이를 텍스트로 처리하고 큰따옴표로 묶습니다.

D. 값 증가

다음 예에서는 JSON_MODIFY 함수를 사용하여 JSON 텍스트의 속성 값을 증분하는 방법을 보여줍니다. 먼저 기존 속성의 값을 가져와 새 키:값 쌍으로 삽입할 수 있습니다. 그런 다음 이전 속성의 값을 .로 설정하여 이전 키를 삭제할 NULL수 있습니다.

DECLARE @stats NVARCHAR(100) = '{"click_count": 173}';
PRINT @stats;

-- Increment value
SET @stats = JSON_MODIFY(@stats, '$.click_count', CAST(JSON_VALUE(@stats, '$.click_count') AS INT) + 1);
PRINT @stats;

결과 집합은 다음과 같습니다.

{
    "click_count": 173
} {
    "click_count": 174
}

E. JSON 개체 수정

JSON_MODIFY은(는) 올바른 형식의 JSON 텍스트가 포함되어 있어도 newValue 인수를 일반 텍스트로 취급합니다. 결과적으로, 다음 예와 같이 함수의 JSON 출력은 큰따옴표로 묶이고 모든 특수 문자는 이스케이프됩니다.

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Update skills array
SET @info = JSON_MODIFY(@info, '$.skills', '["C#","T-SQL","Azure"]');
PRINT @info;

결과 집합은 다음과 같습니다.

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "John",
    "skills": "[\"C#\",\"T-SQL\",\"Azure\"]"
}

자동 이스케이프를 방지하려면 JSON_QUERY 함수를 사용하여 newValue를 제공하세요. JSON_MODIFY은(는) JSON_QUERY이(가) 반환한 값이 올바른 형식의 JSON임을 알고 있으므로 값을 이스케이프하지 않습니다.

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Update skills array
SET @info = JSON_MODIFY(@info, '$.skills', JSON_QUERY('["C#","T-SQL","Azure"]'));
PRINT @info;

결과 집합은 다음과 같습니다.

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "John",
    "skills": ["C#", "T-SQL", "Azure"]
}

F. JSON 열 업데이트

다음 예에서는 JSON이 포함된 테이블 열에서 속성 값을 업데이트합니다.

UPDATE Employee
SET jsonCol = JSON_MODIFY(jsonCol, '$.info.address.town', 'London')
WHERE EmployeeID = 17;

관련 콘텐츠

• JSON 경로 식(SQL Server)
• JSON 데이터(SQL Server)