Inserting JSON data from python into SQL Server-CodePudding

I make a call to an API via a Python script to retrieve data. I store this data into a JSON file using:

  with open('response.json()', 'w') as outfile:
   json5.dump(response.json(), outfile, ensure_ascii=False, indent=4)

Please find below the contents of the JSON file:

 {
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,

}

I have tried extracting the values onto a table in SQL Server but to no avail.

DECLARE @JSON NVARCHAR(MAX)

SELECT @JSON = BulkColumn
FROM OPENROWSET 
(BULK N'[Folder path]', SINGLE_CLOB) 
AS j

SELECT ISJSON(@JSON) AS IsValidJson;

It is not recognising this as valid JSON and getting the error message "JSON text is not properly formatted. Unexpected character 'q' is found at position 7."

All I would like is to extract only the values in the data array into a table as seen below:

enter image description here

Any help would be greatly appreciated.

Thank you

CodePudding user response：

I store this data into a JSON file using:

No, you are not doing that. You are storing the data into a JSON5 file instead.

If you want to store data into a JSON file, use the json module, not the json5 module:

  with open('response.json()', 'w') as outfile:
   json.dump(response.json(), outfile, ensure_ascii=False, indent=4)