ประมวลผลข้อมูล JSON ด้วยฟังก์ชันในตัว

12 นาที

เคล็ดลับ

ดูแท็บ ข้อความและรูปภาพ สําหรับรายละเอียดเพิ่มเติม!

พิจารณาสถานการณ์ที่แอปพลิเคชันอีคอมเมิร์ซของคุณจัดเก็บการกําหนดลักษณะของลูกค้าและข้อมูลเมตาของคําสั่งซื้อเป็นเอกสาร JSON แอปบนอุปกรณ์เคลื่อนที่จะส่งข้อมูลตะกร้าสินค้าในรูปแบบ JSON และระบบการรายงานของคุณจําเป็นต้องส่งออกแค็ตตาล็อกผลิตภัณฑ์เป็น JSON สําหรับ Web API การทํางานโดยตรงกับ JSON ในฐานข้อมูลของคุณช่วยลดความจําเป็นในการแปลงเลเยอร์แอปพลิเคชัน และทําให้การประมวลผลข้อมูลของคุณมีประสิทธิภาพ

ฐานข้อมูล SQL Server, Azure SQL และ SQL ใน Fabric ให้การสนับสนุน JSON ในตัวที่ช่วยให้คุณแยกวิเคราะห์ สืบค้น สร้าง และแปลงข้อมูล JSON ได้โดยตรงใน T-SQL ในหน่วยนี้ คุณจะได้เรียนรู้วิธีใช้ฟังก์ชัน 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_QUERY() เมื่อคุณต้องการรักษาโครงสร้าง JSON ของออบเจ็กต์หรืออาร์เรย์ที่ซ้อนกัน

แยกวิเคราะห์อาร์เรย์ JSON ด้วย `OPENJSON`

OPENJSON เป็นฟังก์ชันที่มีค่าตารางที่แปลงข้อมูล JSON เป็นชุดแถวเชิงสัมพันธ์ ใช้ฟังก์ชันนี้เพื่อรวมข้อมูล JSON กับตารางเชิงสัมพันธ์หรือองค์ประกอบอาร์เรย์กระบวนการทีละรายการ

คิวรีต่อไปนี้แยกวิเคราะห์อาร์เรย์ JSON เป็นแถวที่มี Schema เริ่มต้น:

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

หากไม่มี Schema จะ OPENJSON ส่งกลับสามคอลัมน์ ได้แก่ key (ดัชนีอาร์เรย์หรือชื่อคุณสมบัติ) value (เนื้อหา JSON) และ type (ตัวเลขที่ระบุชนิดข้อมูล JSON: 0=null, 1=string, 2=number, 3=boolean, 4=array, 5=object)

คิวรีต่อไปนี้กําหนด Schema ที่ชัดเจนเพื่อแยกคอลัมน์เฉพาะที่มีชนิดข้อมูลที่เหมาะสม:

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;

Note

เมื่อโค้ดใช้ OPENJSON กับ CROSS APPLYแถวจากตารางหลักที่มี NULL ค่า JSON หรือว่างเปล่าจะไม่ปรากฏในผลลัพธ์ ใช้ OUTER APPLY หากคุณต้องการรวมแถวที่ไม่มีข้อมูล JSON

สร้าง JSON ด้วย `JSON_OBJECT` และ `JSON_ARRAY`

เปิดตัว JSON_OBJECT SQL Server 2022 และ JSON_ARRAY ฟังก์ชันสําหรับการสร้าง JSON ที่ใช้งานง่าย:

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 ฟังก์ชันพร้อมใช้งานใน SQL Server 2022 และใหม่กว่า, ฐานข้อมูล Azure SQL และฐานข้อมูล SQL ใน Microsoft Fabric สําหรับเวอร์ชันก่อนหน้า ให้ใช้ FOR JSON PATH สําหรับฟังก์ชันการทํางานที่คล้ายกัน

ตรวจสอบและตรวจสอบ JSON ด้วย `JSON_CONTAINS`

ข้อมูล JSON จากแหล่งข้อมูลภายนอกอาจมีรูปแบบไม่ถูกต้อง ขาดคุณสมบัติที่คาดไว้ หรือมีค่าที่ไม่คาดคิด การพยายามแยกค่าจาก JSON ที่ไม่ถูกต้องหรือเส้นทางที่ขาดหายไปอาจทําให้เกิดความล้มเหลวในการสืบค้นหรือส่งคืนผลลัพธ์ที่ทําให้ NULL เข้าใจผิดซึ่งปกปิดปัญหาข้อมูล

การประมวลผล JSON ที่มีประสิทธิภาพจําเป็นต้องมีการเข้ารหัสเชิงป้องกัน: ตรวจสอบว่า JSON มีรูปแบบที่ดีก่อนที่จะแยกวิเคราะห์ จากนั้นตรวจสอบว่ามีเส้นทางที่คาดไว้อยู่ก่อนที่จะแยกค่า และตรวจสอบว่าค่าตรงกับความคาดหวังของคุณก่อนที่จะใช้ในตรรกะทางธุรกิจ SQL Server มีฟังก์ชันหลายอย่างเพื่อช่วยคุณตรวจสอบความถูกต้องของเนื้อหา JSON ในแต่ละขั้นตอนของการประมวลผล

ทําความเข้าใจโหมดเส้นทางที่หละหลวมและเข้มงวด

คุณสามารถใช้นิพจน์เส้นทาง JSON ใน 2 โหมดที่ควบคุมการจัดการข้อผิดพลาด

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โหมด (ค่าเริ่มต้น) เมื่อคาดว่าจะมีคุณสมบัติขาดหายไปและควรส่งคืนNULL ใช้ 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

หากต้องการจัดการข้อมูลที่ไม่ถูกต้องอย่างสง่างาม ให้ใช้ISJSONWHEREในส่วนคําสั่งเพื่อกรองแถวที่มี JSON ที่ถูกต้องหรือใน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_CONTAINS ตรวจสอบว่าเอกสาร JSON มีค่าหรือออบเจ็กต์ที่เฉพาะเจาะจงหรือไม่ เช่น ตัวอย่างต่อไปนี้:

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 จึงส่งผลต่อประสิทธิภาพ

พิจารณาตารางที่มีเรกคอร์ดผลิตภัณฑ์ 100,000 รายการ โดยแต่ละแถวมีเอกสาร JSON ที่มีแอตทริบิวต์ผลิตภัณฑ์ การกรองแบบสอบถามตามประเภทต้อง:

อ่านแต่ละแถวจากตาราง
เมื่อต้องการค้นหาคุณสมบัติประเภท ให้แยกวิเคราะห์เอกสาร 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 จะถูกแยกวิเคราะห์เฉพาะระหว่างINSERTUPDATEและการดําเนินการ ไม่ใช่ระหว่างSELECTการสืบค้น

เพิ่มดัชนีเพื่อการกรองที่เร็วขึ้น

ประสิทธิภาพที่แท้จริงมาจากการจัดทําดัชนีคอลัมน์ที่คํานวณ:

-- 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 ส่วนคําสั่ง

แปลงข้อมูลเชิงสัมพันธ์เป็น JSON ด้วย FOR 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 โดยไม่ต้องประมวลผลภายหลัง

สําหรับข้อมูลเพิ่มเติมเกี่ยวกับฟังก์ชัน JSON ใน SQL Server โปรดดู ข้อมูล JSON ใน SQL Server และฟังก์ชัน JSON

คำติชม

หน้านี้มีประโยชน์หรือไม่

ประมวลผลข้อมูล JSON ด้วยฟังก์ชันในตัว

แยกค่าด้วย JSON_VALUE และ JSON_QUERY

แยกวิเคราะห์อาร์เรย์ JSON ด้วย OPENJSON

สร้าง JSON ด้วย JSON_OBJECT และ JSON_ARRAY

รวมข้อมูลด้วย JSON_ARRAYAGG

ตรวจสอบและตรวจสอบ JSON ด้วย JSON_CONTAINS

ทําความเข้าใจโหมดเส้นทางที่หละหลวมและเข้มงวด

เพิ่มประสิทธิภาพการสืบค้น JSON ด้วยคอลัมน์ที่คํานวณ

เหตุใดการแยกวิเคราะห์ JSON จึงส่งผลต่อประสิทธิภาพ

สร้างคอลัมน์ที่คํานวณสําหรับคุณสมบัติ JSON

เพิ่มดัชนีเพื่อการกรองที่เร็วขึ้น

จัดทําดัชนีคุณสมบัติ JSON หลายรายการ

แปลงข้อมูลเชิงสัมพันธ์เป็น JSON ด้วย FOR JSON

คำติชม

แยกวิเคราะห์อาร์เรย์ JSON ด้วย `OPENJSON`

สร้าง JSON ด้วย `JSON_OBJECT` และ `JSON_ARRAY`

รวมข้อมูลด้วย `JSON_ARRAYAGG`

ตรวจสอบและตรวจสอบ JSON ด้วย `JSON_CONTAINS`