SQL OPENJSON -解析JSON数组

zqdjd7g9 于 2023-03-13 发布在其他

关注(0)|答案(1)|浏览(139)

我希望有人能帮我指出正确的方向，从下面的JSON例子中解析数组对象。目前，我能够正确地解析出大部分的JSON。
我遇到的问题是将parentId数组拉到单独的行中，绑定到主ID字段。
下面是JSON的一个示例。

{
  "kind": "folders",
  "data": [
    {
      "id": "IEABQ5EAI4KGTYGQ",
      "accountId": "IEABQ5EA",
      "title": "test prj",
      "createdDate": "2019-04-17T20:39:58Z",
      "updatedDate": "2020-01-16T22:49:09Z",
      "description": "",
      "sharedIds": [
        "KX74YUWR",
        "KUAD47VS",
        "KX75JYTL",
        "KUAEH2GT",
        "KUAERUOG",
        "KX75EJSV",
        "KX75JYTH",
        "KUAEPBXA",
        "KX74ZXJC",
        "KUADJ7OW",
        "KX75JYTF",
        "KX75JQE2",
        "KX75JYTA",
        "KUAC6PRX",
        "KUAD2ZGS",
        "KUADJYXY",
        "KX75JYS5",
        "KUADQEMC",
        "KUADJXDI",
        "KUADAEPD",
        "KX732DQC",
        "KUACJOOM"
      ],
      "parentIds": [
        "IEABQ5EAI4KGTXVG"
      ],
      "childIds": [],
      "scope": "WsFolder",
      "permalink": "www.test.com",
      "workflowId": "IEABQ5EAK776PC4A",
      "customFields": [],
      "customColumnIds": [],
      "project": {
        "authorId": "KUADJXDI",
        "ownerIds": [
          "KUADJXDI"
        ],
        "status": "Completed",
        "customStatusId": "IEABQ5EAJMA6STFV",
        "startDate": "2019-05-29",
        "endDate": "2019-06-18",
        "createdDate": "2019-05-31T04:56:58Z",
        "completedDate": "2020-01-16T22:49:09Z"
      }
    }
  ]
}

下面的SQL代码是我用来解析出主要字段的代码--

SELECT [key] AS Doc_id, id, title, createdDate,  updatedDate, [description], permalink, workflowId,
parentIds, customFields, customColumnIds, project
into #JsonDocTemp
FROM OPENJSON (@WJSON2, '$.data') AS EachDoc
  CROSS APPLY OPENJSON(EachDoc.Value)
     WITH (
             id NVARCHAR(20) N'$.id',
             title NVARCHAR(500) N'$.title',
             createdDate DATETIME '$.createdDate',
             updatedDate DATETIME '$.updatedDate',
             [description] NVARCHAR(MAX) N'$.description',
             permalink NVARCHAR(100) N'$.permalink',
             workflowId NVARCHAR(20) N'$.workflowId',
             parentIds NVARCHAR(MAX) N'$.parentIds' AS JSON,
             customFields NVARCHAR(MAX) N'$.customFields' AS JSON,
             customColumnIds NVARCHAR(MAX) N'$.customColumnIds' AS JSON,
             project NVARCHAR(MAX) N'$.parentIds' AS JSON
        )

我想将临时表中的parentId解析到行中，或者使用.data直接从@WJSON2解析parentId。parentId也可以。
任何帮助-我想我快到了，但我错过了一些东西。
谢谢杰里米

JSON

来源：https://stackoverflow.com/questions/59817138/sql-openjson-parse-json-array

1条答案

按热度按时间

fgw7neuy1#

对于每个嵌套级别，您需要使用附加的APPLY运算符和带有显式模式的OPENJSON()调用：
Json：

DECLARE @json nvarchar(max) = N'{
   "kind":"folders",
   "data":[
      {
         "id":"IEABQ5EAI4KGTYGQ",
         "accountId":"IEABQ5EA",
         "title":"test prj",
         "createdDate":"2019-04-17T20:39:58Z",
         "updatedDate":"2020-01-16T22:49:09Z",
         "description":"",
         "sharedIds":[
            "KX74YUWR",
            "KUAD47VS",
            "KX75JYTL",
            "KUAEH2GT",
            "KUAERUOG",
            "KX75EJSV",
            "KX75JYTH",
            "KUAEPBXA",
            "KX74ZXJC",
            "KUADJ7OW",
            "KX75JYTF",
            "KX75JQE2",
            "KX75JYTA",
            "KUAC6PRX",
            "KUAD2ZGS",
            "KUADJYXY",
            "KX75JYS5",
            "KUADQEMC",
            "KUADJXDI",
            "KUADAEPD",
            "KX732DQC",
            "KUACJOOM"
         ],
         "parentIds":[
            "IEABQ5EAI4KGTXVG"
         ],
         "childIds":[

         ],
         "scope":"WsFolder",
         "permalink":"www.test.com",
         "workflowId":"IEABQ5EAK776PC4A",
         "customFields":[

         ],
         "customColumnIds":[

         ],
         "project":{
            "authorId":"KUADJXDI",
            "ownerIds":[
               "KUADJXDI"
            ],
            "status":"Completed",
            "customStatusId":"IEABQ5EAJMA6STFV",
            "startDate":"2019-05-29",
            "endDate":"2019-06-18",
            "createdDate":"2019-05-31T04:56:58Z",
            "completedDate":"2020-01-16T22:49:09Z"
         }
      }
   ]
}'

语句（从每个JSON级别仅返回id和parentIds）：

SELECT j1.id, j2.parentId
FROM OPENJSON (@json, '$.data') WITH (
   id nvarchar(50) '$.id',
   parentIds nvarchar(max) '$.parentIds' AS JSON
) j1
OUTER APPLY OPENJSON(j1.parentIds) WITH (
   parentId nvarchar(50) '$'
) j2

结果：

id                  parentId
IEABQ5EAI4KGTYGQ    IEABQ5EAI4KGTXVG

语句（解析其他JSON键和嵌套数组）：

SELECT 
   j1.kind, 
   j2.id, j2.accountId, j2.title, j2.createdDate, j2.updatedDate, j2.description,
   j3.sharedId, j4.parentId, j5.childId,
   j2.scope, j2.permalink, j2.workflowId,
   j6.customField, j7.customColumnId
FROM OPENJSON (@json, '$') WITH (
   kind nvarchar(50) '$.kind',
   data nvarchar(max) '$.data' AS JSON
)j1
OUTER APPLY OPENJSON(j1.data) WITH (
   id nvarchar(50) '$.id',
   accountId nvarchar(50) '$.accountId',
   title nvarchar(50) '$.title',
   createdDate nvarchar(50) '$.createdDate',
   updatedDate nvarchar(50) '$.updatedDate',
   description nvarchar(50) '$.description',
   sharedIds nvarchar(max) '$.sharedIds' AS JSON,
   parentIds nvarchar(max) '$.parentIds' AS JSON,
   childIds nvarchar(max) '$.childIds' AS JSON,
   scope nvarchar(50) '$.scope',
   permalink nvarchar(50) '$.permalink',
   workflowId nvarchar(100) '$.workflowId',
   customFields nvarchar(max) '$.customFields' AS JSON,
   customColumnIds nvarchar(max) '$.customColumnIds' AS JSON
) j2
OUTER APPLY OPENJSON(j2.sharedIds) WITH (
   sharedId nvarchar(50) '$'
) j3
OUTER APPLY OPENJSON(j2.parentIds) WITH (
   parentId nvarchar(50) '$'
) j4
OUTER APPLY OPENJSON(j2.childIds) WITH (
   childId nvarchar(50) '$'
) j5
OUTER APPLY OPENJSON(j2.customFields) WITH (
   customField nvarchar(50) '$'
) j6
OUTER APPLY OPENJSON(j2.customColumnIds) WITH (
   customColumnId nvarchar(50) '$'
) j7

赞(0）回复(0）举报 2023-03-13

我来回答

SQL OPENJSON -解析JSON数组

1条答案

相关问题

热门标签

最新问答