기본 제공 함수를 사용하여 JSON 데이터 처리

12분

팁 (조언)

자세한 내용은 텍스트 및 이미지 탭을 참조하세요.

전자상거래 애플리케이션이 고객 기본 설정을 저장하고 메타데이터를 JSON 문서로 주문하는 시나리오를 고려해 보세요. 모바일 앱은 JSON 형식으로 쇼핑 카트 데이터를 보내고 보고 시스템은 웹 API용 JSON으로 제품 카탈로그를 내보내야 합니다. 데이터베이스에서 JSON으로 직접 작업하면 애플리케이션 계층 변환이 필요하지 않으며 데이터 처리를 효율적으로 유지할 수 있습니다.

Fabric의 SQL Server, Azure SQL 및 SQL 데이터베이스는 T-SQL에서 직접 JSON 데이터를 구문 분석, 쿼리, 만들기 및 변환할 수 있는 기본 제공 JSON 지원을 제공합니다. 이 단원에서는 JSON 함수를 사용하여 값을 추출하고, JSON 출력을 생성하고, 데이터를 JSON 배열로 집계하고, JSON 콘텐츠의 유효성을 검사하는 방법을 알아봅니다.

JSON_VALUE 및 JSON_QUERY 사용하여 값 추출

데이터베이스에 저장된 JSON으로 작업할 때 필터링, 조인 또는 표시를 위한 특정 값을 추출해야 합니다. SQL Server는 이 목적을 위해 다음 두 가지 함수를 제공합니다.

JSON_VALUE() 는 JSON 문자열에서 스칼라 값(문자열, 숫자, 부울)을 추출합니다.

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd",
        "active": true
    },
    "orderTotal": 1599.99
}';

SELECT 
    JSON_VALUE(@json, '$.customer.id') AS CustomerID,
    JSON_VALUE(@json, '$.customer.name') AS CustomerName,
    JSON_VALUE(@json, '$.orderTotal') AS OrderTotal;

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

CustomerID   CustomerName   OrderTotal
----------   ------------   ----------
12345        Contoso Ltd    1599.99

이 함수는 경로 식을 사용하여 JSON 구조를 탐색하고 값을 문자열로 NVARCHAR(4000) 반환합니다. 계산 또는 비교에 필요한 경우 결과를 다른 데이터 형식으로 캐스팅할 수 있습니다.

JSON_QUERY() 는 JSON 개체 또는 배열(비칼라 값)을 추출합니다.

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd"
    },
    "items": [
        {"product": "Widget", "qty": 5},
        {"product": "Gadget", "qty": 3}
    ]
}';

SELECT 
    JSON_QUERY(@json, '$.customer') AS CustomerObject,
    JSON_QUERY(@json, '$.items') AS ItemsArray;

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

CustomerObject                          ItemsArray
--------------------------------------  ------------------------------------------------
{"id": 12345,"name": "Contoso Ltd"}     [{"product": "Widget", "qty": 5},{"product": "Gadget", "qty": 3}]

달리 JSON_VALUE(), JSON_QUERY() JSON 구조를 유지하여 개체와 배열을 저장하거나 다른 함수에 전달하거나 애플리케이션으로 돌아갈 수 있는 유효한 JSON 문자열로 반환합니다.

경로 식은 다음 예제와 같이 중첩된 속성에 대한 점 표기법과 배열 요소에 대한 대괄호 표기법을 사용하여 루트 요소를 나타내는 데 사용합니다 $ .

-- Access array elements by index (0-based)
SELECT JSON_VALUE(@json, '$.items[0].product') AS FirstProduct;

결과는 다음과 같습니다.

FirstProduct
------------
Widget

배열 인덱스는 0부터 시작하므로 $.items[0] 첫 번째 요소를 참조합니다. 이 구문을 사용하여 위치를 알고 있을 때 특정 항목을 추출하거나 모든 배열 요소를 처리해야 하는 경우와 OPENJSON 결합합니다.

팁 (조언)

비교 또는 계산에 스칼라 값이 필요한 경우 사용합니다 JSON_VALUE() . 중첩된 개체 또는 배열의 JSON 구조를 유지해야 하는 경우에 사용합니다 JSON_QUERY() .

`OPENJSON`를 사용하여 JSON 배열을 구문 분석합니다.

OPENJSON 는 JSON 데이터를 관계형 행 집합으로 변환하는 테이블 반환 함수입니다. 이 함수를 사용하여 JSON 데이터를 관계형 테이블과 조인하거나 배열 요소를 개별적으로 처리합니다.

다음 쿼리는 JSON 배열을 기본 스키마가 있는 행으로 구문 분석합니다.

DECLARE @json NVARCHAR(MAX) = N'[
    {"id": 1, "name": "Widget", "price": 29.99},
    {"id": 2, "name": "Gadget", "price": 49.99},
    {"id": 3, "name": "Gizmo", "price": 19.99}
]';

SELECT * FROM OPENJSON(@json);

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

key   value                                          type
---   --------------------------------------------   ----
0     {"id": 1, "name": "Widget", "price": 29.99}   5
1     {"id": 2, "name": "Gadget", "price": 49.99}   5
2     {"id": 3, "name": "Gizmo", "price": 19.99}    5

스키마 OPENJSON 가 없으면 세 개의 열(배열 인덱스 key 또는 속성 이름), value (JSON 콘텐츠) 및 type (JSON 데이터 형식을 나타내는 숫자: 0=null, 1=string, 2=number, 3=boolean, 4=array, 5=object)을 반환합니다.

다음 쿼리는 적절한 데이터 형식의 특정 열을 추출하는 명시적 스키마를 정의합니다.

SELECT 
    ProductID,
    ProductName,
    Price
FROM OPENJSON(@json)
WITH (
    ProductID INT '$.id',
    ProductName NVARCHAR(100) '$.name',
    Price DECIMAL(10,2) '$.price'
);

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

ProductID   ProductName   Price
---------   -----------   ------
1           Widget        29.99
2           Gadget        49.99
3           Gizmo         19.99

WITH 절은 JSON 속성을 유형화된 열에 매핑합니다. 이 방법을 사용하면 계산 및 비교를 위한 적절한 데이터 형식을 제공하고 필요한 속성만 선택할 수 있습니다.

OPENJSON를 사용하여 테이블 데이터와 CROSS APPLY을 결합합니다.

-- Assuming Orders table has a JSON column called OrderDetails
SELECT 
    o.OrderID,
    o.CustomerID,
    items.ProductName,
    items.Quantity,
    items.UnitPrice
FROM Orders AS o
CROSS APPLY OPENJSON(o.OrderDetails)
WITH (
    ProductName NVARCHAR(100) '$.product',
    Quantity INT '$.qty',
    UnitPrice DECIMAL(10,2) '$.price'
) AS items;

비고

코드에서 OPENJSON를 CROSS APPLY와 함께 사용하면 NULL 값을 갖거나 JSON 값이 비어 있는 메인 테이블의 행은 결과에 나타나지 않습니다. JSON 데이터가 없는 행을 포함해야 하는 경우 사용합니다 OUTER APPLY .

JSON을 `JSON_OBJECT` 및 `JSON_ARRAY`로 구성하세요

직관적인 JSON 생성을 위한 SQL Server 2022 도입 JSON_OBJECT 및 JSON_ARRAY 기능:

JSON_OBJECT() 는 키-값 쌍에서 JSON 개체를 만듭니다. 다음 예제에서는 제품에 대한 JSON 개체를 빌드하는 방법을 보여줍니다.

SELECT JSON_OBJECT(
    'id': ProductID,
    'name': Name,
    'price': ListPrice,
    'available': CASE WHEN SellEndDate IS NULL THEN 'true' ELSE 'false' END
) AS ProductJson
FROM SalesLT.Product
WHERE ProductID = 680;

결과는 다음과 같습니다.

ProductJson
---------------------------------------------------------------------------
{"id":680,"name":"HL Road Frame - Black, 58","price":1431.50,"available":"true"}

이 함수는 문자열 값의 특수 문자에 대한 데이터 형식 변환 및 적절한 JSON 이스케이프를 자동으로 처리합니다.

JSON_ARRAY() 는 값에서 JSON 배열을 만듭니다. 다음 예제에서는 JSON 배열을 빌드합니다.

SELECT JSON_ARRAY(
    'SQL Server',
    'Azure SQL Database',
    'SQL Database in Fabric'
) AS Platforms;

결과는 다음과 같습니다.

Platforms
---------------------------------------------------------
["SQL Server","Azure SQL Database","SQL Database in Fabric"]

열 값, 변수 또는 리터럴 값을 JSON_ARRAY()에 전달할 수 있습니다. 함수는 입력 형식에 관계없이 적절한 형식의 JSON 배열을 만듭니다.

그런 다음 이러한 함수를 결합하여 중첩된 JSON 구조를 빌드합니다. 다음 예제에서는 고객과 합계 정보를 사용하여 전체 주문 JSON 개체를 생성합니다.

SELECT JSON_OBJECT(
    'orderId': soh.SalesOrderID,
    'orderDate': soh.OrderDate,
    'customer': JSON_OBJECT(
        'id': c.CustomerID,
        'name': c.CompanyName
    ),
    'totals': JSON_OBJECT(
        'subtotal': soh.SubTotal,
        'tax': soh.TaxAmt,
        'total': soh.TotalDue
    )
) AS OrderJson
FROM SalesLT.SalesOrderHeader AS soh
INNER JOIN SalesLT.Customer AS c
    ON soh.CustomerID = c.CustomerID
WHERE soh.SalesOrderID = 71774;

결과는 다음과 같습니다.

OrderJson
--------------------------------------------------------------------------------
{"orderId":71774,"orderDate":"2008-06-01","customer":{"id":29825,"name":"Contoso"},"totals":{"subtotal":880.35,"tax":70.43,"total":972.79}}

JSON_OBJECT 호출을 중첩하면 애플리케이션에서 예상하는 형식에 맞는 계층 구조를 생성할 수 있습니다. 이 방법은 문자열 연결보다 더 명확하며 유효한 JSON 출력을 보장합니다.

다음을 사용하여 데이터 집계 `JSON_ARRAYAGG`

JSON_ARRAYAGG 는 여러 행의 값을 단일 JSON 배열로 집계합니다. 이 함수는 정규화된 관계형 데이터에서 비정규화된 JSON 출력을 만드는 데 유용합니다.

SELECT 
    c.CustomerID,
    c.CompanyName,
    JSON_ARRAYAGG(soh.SalesOrderID) AS OrderIds
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
    ON c.CustomerID = soh.CustomerID
GROUP BY c.CustomerID, c.CompanyName;

결과는 다음과 같습니다.

CustomerID   CompanyName           OrderIds
----------   -------------------   ------------------
29825        Contoso Retail        [71774,71776,71780]
29847        Adventure Works       [71782,71784]

이 함수는 그룹화된 행에서 일치하는 모든 값을 수집하고 단일 JSON 배열로 결합합니다. 이 메서드는 정규화된 데이터베이스 테이블에서 비정규화된 API 응답을 만드는 데 유용합니다.

복합 개체의 배열을 만들려면 다음과 같이 JSON_ARRAYAGG 결합 JSON_OBJECT 할 수 있습니다.

SELECT 
    pc.Name AS Category,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id': p.ProductID,
            'name': p.Name,
            'price': p.ListPrice
        )
    ) AS Products
FROM SalesLT.ProductCategory AS pc
INNER JOIN SalesLT.Product AS p
    ON pc.ProductCategoryID = p.ProductCategoryID
GROUP BY pc.ProductCategoryID, pc.Name;

결과는 다음과 같습니다.

Category        Products
--------------  --------------------------------------------------------------------------
Road Bikes      [{"id":749,"name":"Road-150 Red, 62","price":3578.27},{"id":750,"name":"Road-150 Red, 44","price":3578.27}]
Mountain Bikes  [{"id":771,"name":"Mountain-100 Silver, 38","price":3399.99},{"id":772,"name":"Mountain-100 Black, 38","price":3374.99}]

중요합니다

JSON_ARRAYAGG 및 JSON_OBJECT/JSON_ARRAY 함수는 MICROSOFT Fabric의 SQL Server 2022 이상, Azure SQL Database 및 SQL 데이터베이스에서 사용할 수 있습니다. 이전 버전의 경우 유사한 기능을 사용합니다 FOR JSON PATH .

다음을 사용하여 JSON 유효성 검사 및 확인 `JSON_CONTAINS`

외부 원본의 JSON 데이터는 형식이 잘못되었거나 예상 속성이 없거나 예기치 않은 값을 포함할 수 있습니다. 잘못된 JSON 또는 누락된 경로에서 값을 추출하려고 하면 쿼리 오류가 발생하거나 데이터 문제를 마스킹하는 잘못된 NULL 결과를 반환할 수 있습니다.

강력한 JSON 처리에는 방어적 코딩이 필요합니다. 구문 분석하기 전에 JSON이 올바른 형식인지 확인합니다. 그런 다음, 값을 추출하기 전에 예상 경로가 있는지 확인하고 비즈니스 논리에 사용하기 전에 값이 예상과 일치하는지 확인합니다. SQL Server는 각 처리 단계에서 JSON 콘텐츠의 유효성을 검사하는 데 도움이 되는 몇 가지 함수를 제공합니다.

lax 및 strict 경로 모드 이해

오류 처리를 제어하는 두 가지 모드에서 JSON 경로 식을 사용할 수 있습니다.

DECLARE @json NVARCHAR(MAX) = N'{"name": "Widget", "price": 29.99}';

-- Lax mode (default): Returns NULL for missing paths
SELECT JSON_VALUE(@json, 'lax $.description') AS LaxResult;

-- Strict mode: Raises an error for missing paths
SELECT JSON_VALUE(@json, 'strict $.description') AS StrictResult;

결과는 다음과 같습니다.

LaxResult
---------
NULL

-- Strict mode raises: Property cannot be found on the specified JSON path.

누락된 속성이 예상될 때, 반환값으로 lax이 될 수 있도록 기본값인 lax 모드를 사용하세요. 누락된 속성이 오류를 일으켜야 하는 데이터 문제를 나타낼 때 strict 모드를 사용합니다.

ISJSON 문자열에 유효한 JSON이 포함되어 있는지 여부를 확인합니다. 다음 예제에서는 ISJSON을 사용하는 방법을 보여 줍니다.

SELECT 
    ISJSON('{"name": "test"}') AS ValidJson,      -- Returns 1
    ISJSON('not valid json') AS InvalidJson,       -- Returns 0
    ISJSON(NULL) AS NullJson;                      -- Returns NULL

결과는 다음과 같습니다.

ValidJson   InvalidJson   NullJson
---------   -----------   --------
1           0             NULL

잘못된 데이터를 적절히 처리하려면, 유효한 JSON이 있는 행을 필터링하기 위해 ISJSON를 WHERE 절에서 사용하거나 CASE 식에서 사용합니다.

JSON_PATH_EXISTS 다음 예제와 같이 JSON 문서에 특정 경로가 있는지 확인합니다.

DECLARE @json NVARCHAR(MAX) = N'{"customer": {"name": "Contoso", "tier": "Gold"}}';

SELECT 
    JSON_PATH_EXISTS(@json, '$.customer.name') AS HasName,
    JSON_PATH_EXISTS(@json, '$.customer.email') AS HasEmail;

결과는 다음과 같습니다.

HasName   HasEmail
-------   --------
1         0

이 함수는 경로가 있으면 1을 반환하고, 그렇지 않으면 0을 반환합니다. 엄격한 모드에서 JSON_VALUE를 호출하기 전에 사용하거나 다양한 구조의 JSON을 조건에 따라 처리하십시오.

다음 예제와 같이 JSON 문서에 특정 값 또는 개체가 포함되어 있는지 확인하는 데 사용합니다 JSON_CONTAINS .

DECLARE @json NVARCHAR(MAX) = N'{"tags": ["sql", "database", "azure"]}';

SELECT 
    JSON_CONTAINS(@json, '"sql"', '$.tags') AS HasSqlTag,
    JSON_CONTAINS(@json, '"python"', '$.tags') AS HasPythonTag;

결과는 다음과 같습니다.

HasSqlTag   HasPythonTag
---------   ------------
1           0

계산 열을 사용하여 JSON 쿼리 최적화

특정 JSON 속성을 자주 쿼리하는 경우 데이터베이스 엔진은 모든 쿼리의 모든 행에 대해 JSON 문서를 구문 분석해야 합니다. 수천 또는 수백만 개의 행이 있는 테이블의 경우 이 반복 구문 분석으로 인해 상당한 오버헤드가 발생합니다. 계산 열을 사용하면 JSON 값을 한 번 추출하고 인덱싱을 지원하는 쿼리 가능한 형식으로 저장할 수 있습니다.

JSON 구문 분석이 성능에 영향을 미치는 이유

각 행에 제품 특성이 있는 JSON 문서가 포함된 100,000개의 제품 레코드가 있는 테이블을 생각해 보세요. 범주별 쿼리 필터링은 다음을 수행해야 합니다.

테이블에서 각 행 읽기
범주 속성을 찾으려면 JSON 문서를 구문 분석합니다.
값 추출 및 비교

최적화가 없으면 간단한 필터도 모든 행에서 JSON 구문 분석으로 전체 테이블 검색이 필요합니다.

JSON 속성에 대한 계산 열 만들기

계산 열은 JSON 속성을 자동으로 추출하고 다음 예제와 같이 일반 열로 사용할 수 있도록 합니다.

-- Add a computed column that extracts a JSON property
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category');

-- The column is now available in queries
SELECT ProductID, ProductName, ProductCategory
FROM Products
WHERE ProductCategory = 'Electronics';

결과는 다음과 같습니다.

ProductID   ProductName           ProductCategory
---------   -------------------   ---------------
101         Wireless Mouse        Electronics
102         USB Keyboard          Electronics
103         HD Monitor            Electronics

기본적으로 계산 열은 가상입니다. 데이터베이스는 쿼리 시 값을 계산하지만 JSON 추출을 최적화할 수 있습니다. 성능을 향상시키려면 다음 예제와 같이 계산 열을 유지할 수 있습니다.

-- Persisted computed column stores the extracted value physically
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED;

지속형 열은 추출된 값을 디스크에 저장하므로 JSON은 쿼리 중이 아니라 INSERT 및 UPDATE 작업 중에만 구문 분석됩니다.

더 빠른 필터링을 위한 인덱스 추가

실제 성능 향상은 계산 열을 인덱싱할 때 발생합니다.

-- Create an index on the computed column
CREATE INDEX IX_Products_Category ON Products(ProductCategory);

-- Now this query uses an index seek instead of a table scan
SELECT ProductID, ProductName
FROM Products
WHERE ProductCategory = 'Electronics';

인덱스가 없으면 쿼리는 100,000개의 행을 모두 검색합니다. 인덱스를 사용하여 쿼리 엔진은 인덱스 검색을 수행하고 일치하는 행만 검색합니다. 이 인덱스는 쿼리 시간을 초에서 밀리초로 줄일 수 있습니다.

여러 JSON 속성 인덱싱

여러 JSON 속성을 필터링하는 쿼리의 경우 계산 열 및 복합 인덱스를 만듭니다.

-- Extract multiple properties
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED,
    ProductBrand AS JSON_VALUE(ProductData, '$.brand') PERSISTED,
    ProductPrice AS CAST(JSON_VALUE(ProductData, '$.price') AS DECIMAL(10,2)) PERSISTED;

-- Create a composite index for common query patterns
CREATE INDEX IX_Products_Category_Brand ON Products(ProductCategory, ProductBrand);

-- Create an index for price range queries
CREATE INDEX IX_Products_Price ON Products(ProductPrice);

이제 범주 및 브랜드별로 필터링하거나 가격별로 정렬하는 쿼리는 이러한 인덱스를 효율적으로 사용할 수 있습니다.

팁 (조언)

자주 액세스하는 JSON 속성의 경우 인덱스가 있는 계산 열은 쿼리 시간에 JSON을 구문 분석하는 것에 비해 쿼리 성능을 향상시킬 수 있습니다. 쿼리 패턴을 모니터링하고, WHERE, JOIN 또는 ORDER BY 절에서 사용되는 속성에 대한 계산된 열을 생성하세요.

FOR JSON을 사용하여 관계형 데이터를 JSON으로 변환

쿼리에서 포괄적인 JSON 출력을 얻으려면 FOR JSON PATH 또는 FOR JSON AUTO을(를) 사용하십시오.

SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    pc.Name AS CategoryName
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 1000
FOR JSON PATH, ROOT('products');

결과는 다음과 같습니다.

{"products":[{"ProductID":749,"Name":"Road-150 Red, 62","ListPrice":3578.27,"CategoryName":"Road Bikes"},{"ProductID":750,"Name":"Road-150 Red, 44","ListPrice":3578.27,"CategoryName":"Road Bikes"}]}

FOR JSON PATH 에서는 열 별칭을 통해 JSON 구조를 제어할 수 있습니다. 별칭에 점 표기법을 사용하여 중첩된 개체를 만듭니다.

SELECT 
    p.ProductID AS 'product.id',
    p.Name AS 'product.name',
    pc.Name AS 'product.category'
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID = 680
FOR JSON PATH;

결과는 다음과 같습니다.

[{"product":{"id":680,"name":"HL Road Frame - Black, 58","category":"Road Frames"}}]

열 별칭 'product.id'은 product 속성을 가진 중첩된 id 개체를 만듭니다. 이 기술을 사용하면 후처리 없이 API의 예상 형식과 일치하도록 출력을 셰이프할 수 있습니다.

SQL Server의 JSON 함수에 대한 자세한 내용은 SQL Server 및 JSON 함수의 JSON 데이터를 참조 하세요.

피드백

이 페이지가 도움이 되었나요?

기본 제공 함수를 사용하여 JSON 데이터 처리

JSON_VALUE 및 JSON_QUERY 사용하여 값 추출

OPENJSON를 사용하여 JSON 배열을 구문 분석합니다.

JSON을 JSON_OBJECT 및 JSON_ARRAY로 구성하세요

다음을 사용하여 데이터 집계 JSON_ARRAYAGG

다음을 사용하여 JSON 유효성 검사 및 확인 JSON_CONTAINS

lax 및 strict 경로 모드 이해

계산 열을 사용하여 JSON 쿼리 최적화

JSON 구문 분석이 성능에 영향을 미치는 이유

JSON 속성에 대한 계산 열 만들기

더 빠른 필터링을 위한 인덱스 추가

여러 JSON 속성 인덱싱

FOR JSON을 사용하여 관계형 데이터를 JSON으로 변환

피드백

`OPENJSON`를 사용하여 JSON 배열을 구문 분석합니다.

JSON을 `JSON_OBJECT` 및 `JSON_ARRAY`로 구성하세요

다음을 사용하여 데이터 집계 `JSON_ARRAYAGG`

다음을 사용하여 JSON 유효성 검사 및 확인 `JSON_CONTAINS`