SQL Server JSON FOR JSON & Functions

2023-12-14

筆記 SQL Server 產生 JSON 以及各式 JSON Functions 的使用方式。

FOR JSON

SQL Server 2016 (13.x) 以後就支援 FOR JSON 的方式，能夠輕鬆的將關聯式資料表產生為 JSON。

如果資料表之間有關聯，操作起來更是輕鬆愜意。

SELECT
  C.CategoryID,
  C.CategoryName,
  C.Description,
  Products.ProductID,
  Products.ProductName,
  Products.UnitPrice
FROM Categories C,
     Products "Products"
FOR JSON AUTO

沒有關聯也可以透過子查詢的方式來組成。

SELECT 
  CategoryID, 
  CategoryName, 
  Description,
  (
	SELECT
	  ProductID,
	  ProductName,
	  UnitPrice
	FROM Products AS SUB
	WHERE SUB.CategoryID = MAIN.CategoryID
	FOR JSON PATH
  ) "Products"
FROM Categories AS MAIN
FOR JSON PATH

Python

對比以往透過 Python 的處理方式 😅

def get_result():
  connection_string = 'DRIVER={ODBC Driver 17 for SQL Server};...'
  with pyodbc.connect(connection_string) as connection, connection.cursor() as cursor:
      sql_query = """
      SELECT 
        C.CategoryID, 
        C.CategoryName, 
        C.Description,
        P.ProductID,
        P.ProductName,
        P.UnitPrice
      FROM Categories C
      LEFT JOIN Products P ON C.CategoryID = P.CategoryID
      ORDER BY C.CategoryID, P.ProductID
      """
      cursor.execute(sql_query)
      return cursor.fetchall()

result = {}

# Fetch rows and process them
for row in get_result():
  category_id = row.CategoryID

  if category_id not in result:
    result[category_id] = {
      "CategoryID": category_id,
      "CategoryName": row.CategoryName,
      "Description": row.Description,
      "Products": []
    }

  result[category_id]["Products"].append({
    "ProductID": row.ProductID,
    "ProductName": row.ProductName,
    "UnitPrice": row.UnitPrice
  })

# Convert the result to a list of dictionaries
categories = list(result.values())
json_output = json.dumps(categories, indent=2)

Format query results as JSON with FOR JSON | learn.microsoft

JSON Functions

FOR JSON 是將關聯式資料表轉換為 JSON OUTPUT，JSON Functions 則是直接處理儲存在關聯式料表欄位當中的 JSON Strings。

儲存 JSON 使用的 DATA TYPE 依照有沒有使用 Unicode 字元的需求，若有選擇 NVARCHAR(MAX) ，若沒有則選擇 VARCHAR(MAX)。