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

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?

CodePudding user response：

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;