OpenJSON商店以原始形式处理的对象

发布于 2025-02-10 08:59:03 字数 1071 浏览 2 评论 0原文

DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'

SELECT *
FROM OPENJSON ( @JSON)  
WITH (   
            OrderNumber   VARCHAR(200)  ,  
            OrderDate     VARCHAR(200)      ,  
            AccountNumber NVARCHAR(MAX)  ,  
            ItemPrice NVARCHAR(MAX)            ,  
            ItemQuantity NVARCHAR(MAX)     ,
            OriginalObject NVARCHAR(MAX)  AS JSOn       
 )

我想在原始观察表列中存储处理后的对象，即每个JSON对象的原始形式。

我已经进行了很多搜索，但找不到任何解决方案。

我想存储原始的JSON对象，以便随着其增加性能问题而无需将其行转换为JSON。我使用上面的列获取Orignal Data。

SQL Server的版本：Microsoft SQL Server 2017 （RTM -CU21）（KB4557397）-14.0.3335.7（x64）

原文

DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'

SELECT *
FROM OPENJSON ( @JSON)  
WITH (   
            OrderNumber   VARCHAR(200)  ,  
            OrderDate     VARCHAR(200)      ,  
            AccountNumber NVARCHAR(MAX)  ,  
            ItemPrice NVARCHAR(MAX)            ,  
            ItemQuantity NVARCHAR(MAX)     ,
            OriginalObject NVARCHAR(MAX)  AS JSOn       
 )

I want to store processed object i.e each json object in its original form in OriginalObject table column.

I have done quite bit searching but did not find any solution.

I want to store original json object so that no need to convert it row to json as its increases performance problem.i get orignal data using above column.

version of sql server:Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

时光清浅 2025-02-17 08:59:03

您可以使用openjson没有模式将数组分解为单个对象，然后apply openjson 函数以获取完整的架构。

DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]';

SELECT
  o.*,
  array.value AS OriginalObject
FROM OPENJSON(@JSON) array
CROSS APPLY OPENJSON(array.value)
  WITH (   
    OrderNumber   varchar(200),  
    OrderDate     datetime,
    AccountNumber varchar(50),
    ItemPrice     decimal(18,9),  
    ItemQuantity  int
 ) o;

我建议您更仔细地选择数据类型。

You can use OPENJSON without a schema to break out the array into single objects, then APPLY again the OPENJSON function to get the full schema.

DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]';

SELECT
  o.*,
  array.value AS OriginalObject
FROM OPENJSON(@JSON) array
CROSS APPLY OPENJSON(array.value)
  WITH (   
    OrderNumber   varchar(200),  
    OrderDate     datetime,
    AccountNumber varchar(50),
    ItemPrice     decimal(18,9),  
    ItemQuantity  int
 ) o;

db<>fiddle