다음을 통해 공유

JSON_MODIFY(Transact-SQL)

  • 아티클

적용 대상: Microsoft Fabric의 Microsoft Fabric Warehouse에 있는 SQL Server 2016(13.x) 이상 Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL 분석 엔드포인트

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;

관련 콘텐츠