时间戳

首页 > 解决方案 > 如何在 SQL Server 的一个字段中插入所有数组元素？

arrays - 如何在 SQL Server 的一个字段中插入所有数组元素？

问题描述

我有一个名为“RecordIds”的列。我正在将数据从 json 导入到 sql server 。我有这个 json 文件。但是当我在表中插入所有内容但在最后三个字段中时，我在数据库表中没有任何值。我怎么解决这个问题 ？

{
  "Id": 1,
  "ImageName": "person1",
  "PersonName": "C1-C4:stretch",
  "GroupId": 1,
  "Text": "Sam has complete C4 tetraplegia. He sits in his power wheelchair all day and is unable to move his arms.<br/><br/>The aim of Sam`s programme is to prevent contracture.<br/><br/>This programme is suitable for people with C1-C4 tetraplegia.",
  "RecordIds": [511, 517, 569, 571, 688, 1037],
  "DropdownIds": [1, 56, 58],
  "TextTitleIds": [3, 5, 7]
}

这是我的查询

declare @details varchar(max)

select @details = 
BulkColumn
from openrowset(BULK'D:\JSON\mmh\examples.json', single_blob) json

if(ISJSON(@details) = 1)
  begin
    print 'Valid Json'
    insert into [dbo].[Examples]
    select * from
    openjson(@details, '$.example')
    with(
        id                      smallint        '$.Id',
        [ImageName]             varchar(50)     '$.ImageName',
        [PersonName]            varchar(50)     '$.PersonName',
        [GroupId]               smallint        '$.GroupId',
        [Text]                  varchar(50)     '$.Text',
        [RecordIds]             varchar(50)     '$.RecordIds',
        [DropdownIds]           varchar(50)     '$.DropdownIds',
        [TextTitleIds]          varchar(50)     '$.TextTitleIds'

    )

 end
else
begin
    print 'invalid Json'
end

我得到了这个输出

RecordIds            DropdownIds               TextTitleIds
NULL                  NULL                         NULL

标签： arraysjsonsql-serverdatabaseinsert

解决方案

您需要使用AS JSON，例如

declare @json nvarchar(max) = 
'{
"example":
  {
      "Id": 1,
      "ImageName": "person1",
      "PersonName": "C1-C4:stretch",
      "GroupId": 1,
      "Text": "Sam has complete C4 tetraplegia. He sits in his power wheelchair all day and is unable to move his arms.<br/><br/>The aim of Sam`s programme is to prevent contracture.<br/><br/>This programme is suitable for people with C1-C4 tetraplegia.",
      "RecordIds": [511, 517, 569, 571, 688, 1037],
      "DropdownIds": [1, 56, 58],
      "TextTitleIds": [3, 5, 7]
  }
}'

  select * from
    openjson(@json, '$.example')
    with(
        id                      smallint        '$.Id',
        [ImageName]             varchar(50)     '$.ImageName',
        [PersonName]            varchar(50)     '$.PersonName',
        [GroupId]               smallint        '$.GroupId',
        [Text]                  varchar(50)     '$.Text',
        [RecordIds]             nvarchar(MAX)   '$.RecordIds' as json,
        [DropdownIds]           nvarchar(MAX)   '$.DropdownIds' as json,
        [TextTitleIds]          nvarchar(MAX)   '$.TextTitleIds' as json

    )

推荐阅读