Traversing JSON via OpenJSON in T-SQL

Question

Context: I have a JSON document with the following form saved to a column in a temp table in SQL Server

[{"File": {"File_Name": "SCAN_6X_AHMC_AAPC_837I_11182019_11242019.txt", "Last_Modified": "Lastmodified:20191125.121049", "File_Size": "Filesize:7196"}}, {"File": {"File_Name": "SCAN_6X_AHMC_AAPC_837P_11182019_11242019.txt", "Last_Modified": "Lastmodified:20191125.121017", "File_Size": "Filesize:3949"}}]

Question: I am trying to extract the file names, modified dates and file sizes from the JSON using T-SQL's OPENJSON function. With the code block below I'm pulling NULL values in the fields I'm trying to get at. Why? And how can I fix this?

SELECT ##jsondump.my_json, jsn.[file_name], jsn.last_modified, jsn.file_size
FROM ##jsondump
OUTER APPLY (

    SELECT * FROM OPENJSON(##jsondump.my_json, '$.File')
    WITH (
        [file_name] NVARCHAR(50) '$.File_Name',
        last_modified NVARCHAR(50) '$.Last_Modified',
        file_size NVARCHAR(50) '$.File_Size'
         )

            ) AS jsn

Similarly this method also doesnt work. Thoughts/suggestions?

DECLARE @json NVARCHAR(MAX)
SET @json = (SELECT my_json FROM ##jsondump)

SELECT json_column.*, 
       JSON_VALUE([value], '$.File_Name') As [File_Name],
       JSON_VALUE([value], '$.Last_Modified') As Last_Modified,
       JSON_VALUE([value], '$.File_Size') As File_Size 
FROM OPENJSON(@json) as json_column

Zhorov · Accepted Answer · 2019-12-08T18:25:18.177

You need to use OPENJSON() with explicit schema and AS JSON option to specify that the referenced property contains an inner JSON object:

JSON:

DECLARE @json nvarchar(max) = N'[
   {
      "File":{
         "File_Name":"SCAN_6X_AHMC_AAPC_837I_11182019_11242019.txt",
         "Last_Modified":"Lastmodified:20191125.121049",
         "File_Size":"Filesize:7196"
      }
   },
   {
      "File":{
         "File_Name":"SCAN_6X_AHMC_AAPC_837P_11182019_11242019.txt",
         "Last_Modified":"Lastmodified:20191125.121017",
         "File_Size":"Filesize:3949"
      }
   }
]'

Statement:

SELECT j2.*
FROM OPENJSON(@json) WITH (
   [File] nvarchar(max) '$.File' AS JSON
) j1
CROSS APPLY OPENJSON(j1.[File]) WITH (
   File_Name nvarchar(100) '$.File_Name',
   Last_Modified nvarchar(100) '$.Last_Modified',
   File_Size nvarchar(100) '$.File_Size'
) j2

Statement with table:

SELECT t.my_json, j2.[File_Name], j2.Last_Modified, j2.File_Size
FROM ##jsondump t
CROSS APPLY OPENJSON(t.my_json) WITH (
   [File] nvarchar(max) '$.File' AS JSON
) j1
CROSS APPLY OPENJSON(j1.[File]) WITH (
   File_Name nvarchar(100) '$.File_Name',
   Last_Modified nvarchar(100) '$.Last_Modified',
   File_Size nvarchar(100) '$.File_Size'
) j2

score 2 · Answer 2 · edited Dec 08 '19 at 20:34

You were very close with your attempt. You don't need to put '$.File' in your OPENJSON statement, but in the WITH:

DECLARE @JSON nvarchar(MAX) = N'[{"File": {"File_Name": "SCAN_6X_AHMC_AAPC_837I_11182019_11242019.txt", "Last_Modified": "Lastmodified:20191125.121049", "File_Size": "Filesize:7196"}}, {"File": {"File_Name": "SCAN_6X_AHMC_AAPC_837P_11182019_11242019.txt", "Last_Modified": "Lastmodified:20191125.121017", "File_Size": "Filesize:3949"}}]';

SELECT *
FROM (VALUES(@JSON))V(J)
     CROSS APPLY OPENJSON(V.J) WITH
                 (file_name nvarchar(50) '$.File.File_Name',
                  last_modified nvarchar(50) '$.File.Last_Modified',
                  file_size nvarchar(50) '$.File.File_Size') OJ;

Excellent approach, probably more appropriate here. – Zhorov Dec 08 '19 at 18:34 — Zhorov, Dec 08 '19 at 18:34

Traversing JSON via OpenJSON in T-SQL

2 Answers2