Skip to main content

Posts

Showing posts from November, 2022

SQL Server tables with JSON

We can indeed store json data as-is into a traditional Microsoft SQL Server database. The document hosted on Microsoft's site left a lot of questions and unknowns that I had to explore and experiment to figure out the right recipe for creating a table to store json, inserting the data as json and querying for the values of individual keys within the json. Here you go:  --Create a table with an identity column and a nvarchat(max) column to store the individual json documents create table dbo.logs (     _id bigint primary key identity,     json_log nvarchar(max) ); --Add a constraint to the json_log column of the table to ensure that the table accepts only json as value to store ALTER TABLE dbo.logs ADD CONSTRAINT [json_log record should be formatted as JSON] CHECK (ISJSON(json_log)=1); --Insert json into the table insert into dbo.logs values ('{"key": "value"}'); insert into dbo.logs values ('{"key": "value1"}'); --Query for al