How to fetch the json column data from database in SQL Server?

Question

I have created a small demo app using .NET 6 with EF core and I am using Serilog to log exceptions into my database.

Everything is working fine but now I want to display the log table details on my view (cshtml). Current log table columns are:

• Message
• MessageTemplate
• Level
• TimeStamp
• Exception
• LogEvent
• ClientIP

I have created a stored procedure to fetch the log details but the LogEvent column contains json data like below:

{
   "TimeStamp":"2022-07-23T20:00:55.6987337",
   "Level":"Information",
   "Message":"Started executing Privacy",
   "MessageTemplate":"Started executing Privacy",
   "Properties":{
      "ActionId":"ba7d94ab-3758-4a4c-a3ef-2bda514a531c",
      "ActionName":"Serilog_Demo.Controllers.HomeController.Privacy (Serilog_Demo)",
      "RequestId":"SomeRequest",
      "RequestPath":"/Home/Privacy",
      "ConnectionId":"something",
      "MachineName":"Test"
   }
}

My goal is to get "Message","TimeStamp","ClientIP", ActionName","MachinName","RequestPath" from above json object. How do I do that? I followed this link

Which shows how to get the JSON data but it seems like I am making some mistake in fetching the records.

Here is my stored procedure:

CREATE PROCEDURE uspGetApplicationLogs
AS
BEGIN
    SELECT ClientIP, LogEvent 
    FROM ApplicationLogs 
    ORDER BY TimeStamp DESC;

     -- this code is not working for me
    DECLARE @json NVARCHAR(MAX);
    SET @json = (SELECT LogEvent FROM ApplicationLogs)

    SELECT *
    FROM OPENJSON((SELECT TOP 1 LogEvent FROM ApplicationLogs))
    WITH (logDateTime timestamp '$.TimeStamp',
          level nvarchar(255) '$.Level',
          ActionName NVARCHAR(MAX) '$.Properties.ActionName');
END

And here is my table script, in case if anybody needs it.

CREATE TABLE [dbo].[ApplicationLogs]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Message] [nvarchar](max) NULL,
    [MessageTemplate] [nvarchar](max) NULL,
    [Level] [nvarchar](max) NULL,
    [TimeStamp] [datetime] NULL,
    [Exception] [nvarchar](max) NULL,
    [LogEvent] [nvarchar](max) NULL,
    [ClientIP] [nvarchar](max) NULL,
    [UserName] [nvarchar](max) NULL,
    [ClientAgent] [nvarchar](max) NULL
)

SET IDENTITY_INSERT [dbo].[ApplicationLogs] ON 
GO

INSERT INTO [dbo].[ApplicationLogs] ([Id], [Message], [MessageTemplate], [Level], [TimeStamp], [Exception], [LogEvent], [ClientIP], [UserName], [ClientAgent]) 
VALUES (1, N'Started executing Privacy', N'Started executing Privacy', N'Information', CAST(N'2022-07-23T20:00:55.700' AS DateTime), NULL, N'{"TimeStamp":"2022-07-23T20:00:55.6987337","Level":"Information","Message":"Started executing Privacy","MessageTemplate":"Started executing Privacy","Properties":{"ActionId":"ba7d94ab-3758-4a4c-a3ef-2bda514a531c","ActionName":"Serilog_Demo.Controllers.HomeController.Privacy (Serilog_Demo)","RequestId":"0E:00000004","RequestPath":"/Home/Privacy","ConnectionId":"SomeConnection","MachineName":"Test"}}', NULL, NULL, NULL)
GO

INSERT INTO [dbo].[ApplicationLogs] ([Id], [Message], [MessageTemplate], [Level], [TimeStamp], [Exception], [LogEvent], [ClientIP], [UserName], [ClientAgent]) 
VALUES (2, N'Attempted to divide by zero.', N'Attempted to divide by zero.', N'Error', CAST(N'2022-07-23T20:00:55.803' AS DateTime), N'System.DivideByZeroException: Attempted to divide by zero.
   at Serilog_Demo.Controllers.HomeController.Privacy() in \Sol_Serilog_Demo\Serilog_Demo\Controllers\HomeController.cs:line 24', N'{"TimeStamp":"2022-07-23T20:00:55.8034293","Level":"Error","Message":"Attempted to divide by zero.","MessageTemplate":"Attempted to divide by zero.","Exception":"System.DivideByZeroException: Attempted to divide by zero.\r\n   at Serilog_Demo.Controllers.HomeController.Privacy() in \Sol_Serilog_Demo\\Serilog_Demo\\Controllers\\HomeController.cs:line 24","Properties":{"ActionId":"ba7d94ab-3758-4a4c-a3ef-2bda514a531c","ActionName":"Serilog_Demo.Controllers.HomeController.Privacy (Serilog_Demo)","RequestId":"4","RequestPath":"/Home/Privacy","ConnectionId":"VIB38TE","MachineName":"Test"}}', NULL, NULL, NULL)
GO

SET IDENTITY_INSERT [dbo].[ApplicationLogs] OFF
GO

I do not want to deserialize the LogEvent column data at .net end. Can anybody help to parse the JSON and get the log event value from my database?

Answer 1

You need to use CROSS APPLY to feed the JSON column into OPENJSON

CREATE PROCEDURE uspGetApplicationLogs
AS

SET NOCOUNT ON;

SELECT
  al.Message,
  al.ClientIP,
  al.LogEvent,
  al.TimeStamp,
  j.*  
FROM ApplicationLogs al
CROSS APPLY OPENJSON(al.LogEvent)
  WITH (
    logDateTime datetime2 '$.TimeStamp',
    level nvarchar(255) '$.Level',
    RequestPath nvarchar(250) '$.Properties.RequestPath',
    MachineName nvarchar(250) '$.Properties.MachineName',
    ActionName nvarchar(250) '$.Properties.ActionName'
  ) j
ORDER BY al.TimeStamp DESC;