时间戳

首页 > 解决方案 > SQL Server：OPENJSON 错误，动态 JSON 列存储在数据库表中

sql - SQL Server：OPENJSON 错误，动态 JSON 列存储在数据库表中

问题描述

我在为我的项目使用 SQL Server JSON 函数的地方遇到以下代码错误。

我想尝试将 json 键值对存储到数据库中的某些表中。

基本上我试图点击返回 JSON 并且我必须存储在我的数据库中的 REST API。

  --- JSON document ---

DECLARE @json nvarchar(max) = N'
   [    
    {
    "ProductId":1,
    "ProductName":"Food",
    "ProductDescription":"Apple",
    "DatePurchased":"1995-05-01T00:00:00"
    },   
    {
    "ProductId":2,
    "ProductName":"Electronics",
    "ProductDescription":"TV",
    "DatePurchased":"2018-09-17T00:00:00"
    }
   ]   
'
-- selecting key values from json
SELECT [Key] 
FROM OPENJSON((@json), '$')

DECLARE @columns varchar(max) = N''
DECLARE @schema varchar(max) = N''
DECLARE @stm varchar(max)

-- Columns preparation
SELECT 
   @columns = CONCAT(@columns, ',', QUOTENAME([key])),
   @schema = CONCAT(@schema, ',', QUOTENAME([key]),' varchar(max) ''$.', [key], '')
FROM OPENJSON(@json, '$[0]')


DROP TABLE IF EXISTS #TestData

-- inserting the json values to db table Statement
SET @stm = 'SELECT '+
       CONCAT(STUFF(@columns, 1, 1, ''),' INTO #TestData FROM OPENJSON((SELECT * FROM @json),''$.records'') WITH (',
   STUFF(@schema, 1, 1, ''),')'
   )

PRINT @stm
EXEC(@stm)


::::: OUTPUT ::::

SELECT 
    [ProductId], [ProductName], [ProductDescription], [DatePurchased] 
INTO #TestData 
FROM OPENJSON((SELECT * FROM @json), '$.records') 
     WITH (
             [ProductId] varchar(max) '$.ProductId,
             [ProductName] varchar(max) '$.ProductName,
             [ProductDescription] varchar(max) '$.ProductDescription,
             [DatePurchased] varchar(max) '$.DatePurchased
          )

我收到这些错误：

Msg 1087, Level 15, State 2, Line 1
必须声明表变量“@json”。

消息 319，级别 15，状态 1，第 1 行
关键字“with”附近的语法不正确。如果此语句是公用表表达式、xmlnamespaces 子句或更改跟踪上下文子句，则前面的语句必须以分号结束

标签： sqlsql-serveropen-json

解决方案

您的查询中有一些错误。我将在下面详细说明，但首先这里有一些应该适用于我认为你正在尝试做的事情：

DECLARE @json nvarchar(max) = N'
   [    
    {
    "ProductId":1,
    "ProductName":"Food",
    "ProductDescription":"Apple",
    "DatePurchased":"1995-05-01T00:00:00"
    },   
    {
    "ProductId":2,
    "ProductName":"Electronics",
    "ProductDescription":"TV",
    "DatePurchased":"2018-09-17T00:00:00"
    }
   ]   
';

DECLARE @columns nvarchar(max) = N'';
DECLARE @schema nvarchar(max) = N'';
DECLARE @stm nvarchar(max);

-- Columns preparation
WITH keys AS (
    SELECT DISTINCT [Key]
    FROM OPENJSON(@json)
    WITH (
        ObjJson NVARCHAR(MAX) N'$' AS JSON
    ) l1
    CROSS APPLY OPENJSON(l1.ObjJson)
)
SELECT
   @columns = CONCAT(@columns, ',', QUOTENAME([key])),
   @schema = CONCAT(@schema, ',', QUOTENAME([key]),' varchar(max) ''$.', [key], '''')
FROM keys;

DROP TABLE IF EXISTS dbo.wip_testdata;

-- inserting the json values to db table Statement
SET @stm = 'SELECT '+
       CONCAT(STUFF(@columns, 1, 1, ''),
       ' INTO dbo.wip_testdata
         FROM OPENJSON(@json)
         WITH (
             ObjJson NVARCHAR(MAX) N''$'' AS JSON
         ) l1
         CROSS APPLY OPENJSON(l1.ObjJson)
         WITH (',
             STUFF(@schema, 1, 1, ''),') l2'
         );

EXEC sp_executesql @stm, N'@json NVARCHAR(MAX)', @json;

SELECT * FROM dbo.wip_testdata;

现在，为了解决这些问题：

  1. 您没有深入挖掘 JSON 以从列名的数组中的对象中获取属性名称。CROSS APPLY地址。在您生成的动态 SQL 语句中也是如此。
  2. 正如评论中提到的，您希望使用sp_executesql以便可以将 JSON 向下传递到动态执行的内部范围。运行动态语句的范围无法@json从外部范围访问，因此您必须将其作为参数传递给sp_executesql. 这就是导致Must declare the table variable "@json"您收到错误的原因。
  3. 范围的另一个问题是您无法访问动态执行返回后动态语句创建的临时表。您将看到我将其转换为物理表，以便您可以在动态语句执行返回后在外部范围内访问它。您可以继续使用临时表，但您需要在执行动态语句之前动态创建该表。动态语句的范围确实可以访问从外部范围预先创建的临时表。

推荐阅读