json - 在 SQL 中读取 API JSON

Question

我正在尝试将 API 直接读入 SQL 数据库。

--Set Header
Declare @header nvarchar(max) = '[{
                                  "Name": "Authorization",
                                  "Value" :"b/XdI6IQzCviZOGJ0E+002DasdoKUFOPmVDkwpQDbQjm3w/qkdxDUzmqvSYEZDCmJGWpA23OTlhFpxRHFz3WOsvay8V58XdIp/UIsr5TpCdMwtW3QsadasdXF2ahwQYp2O6GzKlJciZKZNZjg+W7wqdlB75asdasdasu/p8isu1SLy9b32gS33LR1Ievk/eYZdjPLP4Mx6qZH10yo1UvDfMhHjRw4UrYLOR/44BEW4hON0C+qfessG5+oFgg2J+RrUyfbcIvUz7DG1LUohmyDWJclSC7qqqYRFWVZG2GjwXbeHbTbAfoDa/0Q9iZNanAOJ4ElG/3/hukeFBWQBOI+cGURskFvrHKLGgLReHRM4kZFtlOg+5kzilw6pkH8hqutxT77otJKwH+iYRzH5lgbC"
                                }]';
--Set URL
Declare @wurl varchar(max) = 'https://in901.eu1.qb.com/IDORequestService/MGRestService.svc/json/ue_BBL_MSRPPricings/ItemCode,Country,MSRP/adv/?filter=ItemCode=''KA-2-1-SPD'' AND Country=''AU''' 

Declare @ts as table(Json_Table nvarchar(max))

 insert into @ts
 --Get Account Data
 exec [dbo].APICaller_GET_headers
                            @wurl
                            ,@header

SELECT  *
 FROM OPENJSON((select * from @ts),'$."Items"')  

输出是

我现在正在尝试通过使用 WITH 扩展上述 select 语句来将这些数据写入表中，如下所示

SELECT  *
 FROM OPENJSON((select * from @ts),'$."Items"')  
        WITH (   
              Number   varchar(200)   '$.Name',
              Number   varchar(200)   '$.Value'
                ) a

这是 JSON 输出

[{"Name":"ItemCode","Value":"KA-2-1-SPD"},{"Name":"Country","Value":"AU"},{"Name":"MSRP","Value":"150.00000000"},{"Name":"_ItemId","Value":"PBT=[ue_BBL_MSRPPricing] MSRP.DT=[2020-02-07 13:27:35.097] MSRP.ID=[a4bdcc19-d00c-4fe7-9ad0-4e6b4aaa9cbb]"}]

但它不起作用，并且一直为空。任何的想法？

谢谢

标签： jsonsql-serverapitsql

Answer 1

declare @j nvarchar(max) = N'{"Items":[{"Name":"ItemCode","Value":"KA-2-1-SPD"},{"Name":"Country","Value":"AU"},{"Name":"MSRP","Value":"150.00000000"},{"Name":"_ItemId","Value":"PBT=[ue_BBL_MSRPPricing] MSRP.DT=[2020-02-07 13:27:35.097] MSRP.ID=[a4bdcc19-d00c-4fe7-9ad0-4e6b4aaa9cbb]"}]}';
declare @ts table(Json_Table nvarchar(max));
insert into @ts(Json_Table) values(@j);


select nv.*
from openjson(@j)
with (Items nvarchar(max) as json) as i --Items is array..
cross apply openjson(i.Items) --...cross apply to get each object in the array
with (Name nvarchar(200), Value nvarchar(200)) as nv;

select nv.*
from @ts as ts
cross apply openjson(ts.Json_Table, '$.Items') 
with (Items nvarchar(max) '$' as json) as i
cross apply openjson(i.Items)
with (Name nvarchar(200), Value nvarchar(200)) as nv;

select nv.*
from @ts as ts
cross apply openjson(ts.Json_Table) 
with (Items nvarchar(max) as json) as i
cross apply openjson(i.Items)
with (Name nvarchar(200), Value nvarchar(200)) as nv;

select nv.*
from @ts as ts
cross apply openjson(ts.Json_Table) 
with (Items nvarchar(max) '$.Items' as json) as i
cross apply openjson(i.Items)
with (Name nvarchar(200), Value nvarchar(200)) as nv;


select nv.*
from @ts as ts
cross apply openjson(ts.Json_Table, '$.Items') as i
cross apply openjson(i.value)
with (Name nvarchar(200), Value nvarchar(200)) as nv;