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

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
Read more

A @trello board to get kids excited - continued

This is a continuation of the previous post titled - A trello board to get kids excited . At the time of writing this post, the previous post had 198 page views. I wish people commented. I did get some positive feedback on Twitter. The Trello twitter account re-tweeted my tweet and also sent out a second tweet advertising the page. Thank you very much. I hope a lot of parents and kids benefited and had fun as a result. Trello people: Idea - How about a 'Trello Kiddo'? Perhaps you could offer that to schools that give iPads for each kid to take home with them. Get them when they are young. When a kid does something religiously, regularly and feels great about it and can't wait to tell everyone about it, you know you've done well as a parent. We realized that we needed a separate column to keep track of 'Special accomplishments'. Positive reinforcement that you can see with your eyes! Some parents feel like they haven't done enough for their kids, partic
Read more

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.
Read more