Unable to get data from Json string in sql server database

Question

I am trying to get all the fileDisplayName value from my data set column which has json string.

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;

tried to but failed. my string value in column

"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
      }
   ]
},

excepted result All the fileDisplayName value from field having id - 4805 Result :

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

Zhorov · Answer 1 · 2019-10-18T06:11:57.950

It's difficult without actual table structure, but if I understand you correctly, the next approach may help to get your expected results. You need to parse "value" JSON array with additional APPLY operator using OPENJSON() with explicit schema definition (the WITH clause):

Table:

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
           }
       ]
   }
}'
)

Statement:

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

Output:

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

Unable to get data from Json string in sql server database

1 Answers1