Skip to main content

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 all json values in the table

select * from logs;

--Query to get the specific value of a key in the json

select json_value(json_log, '$.key') from Logs l;

Popular posts from this blog

Create #VirtualPrivateCloud, NAT Instance and NAT Gateways on @AWSCloud

Create a Virtual Private Cloud, NAT instance and the new NAT Gatweay ... and making it all work. This is a YouTube playlist of three videos.

A @trello board to get kids excited

My 8 year old just started his summer break. He did so well in school and I am proud of him. He skipped second grade, got into the gold honor roll in every quarter and got a medal for doing that. Last night, I promised to install a new app for him on his iPad mini. I installed Trello and created a board for him while he watched. I showed him how to create cards, add labels to them and move them from To Do, to Doing to Done. I had him create some cards and label them. He could not stop creating cards. I could not convince him to go to bed after that. He created cards for everything he wants to do in the summer and he is not done with creating cards. He even created a card to email a screenshot of his Trello board to his teacher. Later last night, he was still awake in bed when I checked on him. He told me that he wanted to add three more labels - Math, Science and One-on-One. He wanted a label titled 'One-on-one' for tasks that he wants to do with me and he wants one-on-one at

Cheat sheet to create a #VPC and Subnets on @AWSCloud

One of the critical things to remember for working with a AWS VPC is creating and using it. I had hard time remembering how to do it, so, I wrote down a cheat sheet for myself.  If anyone wants to follow along, just navigate to the VPC page on the AWS Console and start with 'Create VPC' button. Please note that this may cost some dollars if you are not on the free tier. If you are on the free tier and make mistakes, it may cost some dollars. In the steps below, we will be creating the following on a new VPC: An internet gateway One public subnet with routes for accessibility from the internet One private subnet without any routes One EC2 web server with Apache installed in it and serving a sample html page - using the public subnet. One EC2 server with the private subnet and security group that allows access to resources running on the public subnet only.  Create VPC Name tag: myVPC CIDR Block: Tenancy: default (Must have default. Otherwise, it