时间戳

首页 > 解决方案 > 无法从 sql server 数据库中的 Json 字符串获取数据

json - 无法从 sql server 数据库中的 Json 字符串获取数据

问题描述

我正在尝试从具有 json 字符串的数据集列中获取所有 fileDisplayName 值。

select 
id_ref id,
JSON_VALUE(cast(data_map as varchar(max)),'$.dataMap."4805".fileDisplayName') Attachments,
data_map dataa
from action_data where id_ref = 54432;

尝试但失败了。我在列中的字符串值

"4805":{
   "@class":"com.tbitsglobal.dms.persistence.entities.datatypes.FileDataList",
   "id":0,
   "value":[
      {
         "@class":"com.tbitsglobal.dms.persistence.entities.datatypes.FileData",
         "id":0,
         "value":"28b1d5fb-d4b9-4d2b-8f70-cca1ecc6b64a",
         "checkSum":"91df90d0d72b71d46a633150d3f94117",
         "fileDisplayName":"KSTPP-LTP-LTSO-L-622-0003.pdf",
         "fileUploadTime":1571372081959,
         "fileSize":101032,
         "fileProperties":null
      },
      {
         "@class":"com.tbitsglobal.dms.persistence.entities.datatypes.FileData",
         "id":0,
         "value":"4a6eabd6-9df8-4ce7-9eb0-25e6875a5a64",
         "checkSum":"a941428a17726c157915293db64aaca9",
         "fileDisplayName":"KSTPP-LTP-LTSO-L-622-0001.pdf",
         "fileUploadTime":1571372082185,
         "fileSize":85028,
         "fileProperties":null
      },
      {
         "@class":"com.tbitsglobal.dms.persistence.entities.datatypes.FileData",
         "id":0,
         "value":"fb9a37e8-65b8-47a7-8e57-577c5aa4f162",
         "checkSum":"2f0036e074f28d22247608581aeac9ca",
         "fileDisplayName":"KSTPP-LTP-LTSO-L-622-0002.pdf",
         "fileUploadTime":1571372082186,
         "fileSize":86907,
         "fileProperties":null
      }
   ]
},

例外结果来自具有 id 的字段的所有 fileDisplayName 值 - 4805 结果：

KSTPP-LTP-LTSO-L-622-0003.pdf
KSTPP-LTP-LTSO-L-622-0001.pdf
KSTPP-LTP-LTSO-L-622-0002.pdf

标签： jsonsql-serversql-server-2017-express

解决方案

没有实际的表结构很难，但如果我理解正确，下一种方法可能有助于获得预期的结果。您需要使用带有显式模式定义（子句）的"value"附加运算符解析 JSON 数组：APPLYOPENJSON()WITH

桌子：

CREATE TABLE #Data (
   data_map nvarchar(max)
)
INSERT INTO #Data
   (data_map)
VALUES
   (
N'{
   "4805": {
       "@class":"com.tbitsglobal.dms.persistence.entities.datatypes.FileDataList",
       "id":0,
       "value":[
           {
           "@class":"com.tbitsglobal.dms.persistence.entities.datatypes.FileData","id":0,"value":"28b1d5fb-d4b9-4d2b-8f70-cca1ecc6b64a","checkSum":"91df90d0d72b71d46a633150d3f94117","fileDisplayName":"KSTPP-LTP-LTSO-L-622-0003.pdf","fileUploadTime":1571372081959,"fileSize":101032,"fileProperties":null
           },
           {
           "@class":"com.tbitsglobal.dms.persistence.entities.datatypes.FileData","id":0,"value":"4a6eabd6-9df8-4ce7-9eb0-25e6875a5a64","checkSum":"a941428a17726c157915293db64aaca9","fileDisplayName":"KSTPP-LTP-LTSO-L-622-0001.pdf","fileUploadTime":1571372082185,"fileSize":85028,"fileProperties":null
           },
           {
           "@class":"com.tbitsglobal.dms.persistence.entities.datatypes.FileData","id":0,"value":"fb9a37e8-65b8-47a7-8e57-577c5aa4f162","checkSum":"2f0036e074f28d22247608581aeac9ca","fileDisplayName":"KSTPP-LTP-LTSO-L-622-0002.pdf","fileUploadTime":1571372082186,"fileSize":86907,"fileProperties":null
           }
       ]
   }
}'
)

陈述：

SELECT j.*
FROM #Data d
CROSS APPLY OPENJSON(d.data_map, '$."4805".value') WITH (
   fileDisplayName nvarchar(max) '$.fileDisplayName'
) j

输出：

fileDisplayName
KSTPP-LTP-LTSO-L-622-0003.pdf
KSTPP-LTP-LTSO-L-622-0001.pdf
KSTPP-LTP-LTSO-L-622-0002.pdf

推荐阅读