我通過 Python 腳本呼叫 API 來檢索資料。我使用以下方法將此資料存盤到 JSON 檔案中：
with open('response.json()', 'w') as outfile:
json5.dump(response.json(), outfile, ensure_ascii=False, indent=4)
請在下面找到 JSON 檔案的內容：
{
query: {
dimensions: [
"Products.desc",
"Products.category",
],
measures: [
"Products.price",
"Products.amount",
],
filters: [
{
operator: "inDateRange",
values: [
"2021-11-12",
],
member: "Products.createdDate",
},
{
operator: "equals",
values: [
"Scotland",
],
member: "Products.region",
},
],
timezone: "UTC",
order: [],
timeDimensions: [],
},
data: [
{
"Products.desc": "Pens",
"Products.category": "Stationery",
"Products.price": "10.15000000",
"Products.amount": "5",
},
{
"Products.desc": "Bags",
"Products.category": "Clothing",
"Products.price": "40.5000000",
"Products.amount": "10",
},
{
"Products.desc": "Drink",
"Products.category": "Foods",
"Products.price": "15.2000000",
"Products.amount": "20",
},
],
lastRefreshTime: "2021-12-15T12:48:10.230Z",
annotation: {
measures: {
"Products.price": {
title: "Products Price",
shortTitle: "Products Total Price",
type: "number",
drillMembers: [],
drillMembersGrouped: {
measures: [],
dimensions: [],
},
},
"Products.amount": {
title: "Products Amount",
shortTitle: "Products Total Amount",
type: "number",
drillMembers: [],
drillMembersGrouped: {
measures: [],
dimensions: [],
},
},
},
dimensions: {
"Products.desc": {
title: "Products description",
shortTitle: "Desc",
type: "string",
},
"Products.category": {
title: "Products category",
shortTitle: "Category",
type: "string",
},
segments: {},
timeDimensions: {},
},
slowQuery: false,
}
我曾嘗試將值提取到 SQL Server 中的表中，但無濟于事。
DECLARE @JSON NVARCHAR(MAX)
SELECT @JSON = BulkColumn
FROM OPENROWSET
(BULK N'[Folder path]', SINGLE_CLOB)
AS j
SELECT ISJSON(@JSON) AS IsValidJson;
它沒有將其識別為有效的 JSON 并收到錯誤訊息“JSON 文本的格式不正確。在位置 7 處發現了意外字符 'q'。”
我想要的只是將資料陣列中的值提取到一個表中，如下所示：
在此處輸入圖片說明
任何幫助將不勝感激。
謝謝
uj5u.com熱心網友回復：
我使用以下方法將此資料存盤到 JSON 檔案中：
不，你不會那樣做。您將資料存盤到JSON5檔案中。
如果要將資料存盤到 JSON 檔案中，請使用json模塊，而不是json5模塊：
with open('response.json()', 'w') as outfile:
json.dump(response.json(), outfile, ensure_ascii=False, indent=4)
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/382256.html
標籤：Python sql 数组 json sql-server
下一篇：遞回呼叫以列印數字串列