Posts Tagged json

Generating Class(es) from JSON in Visual Studio

JSON is a very popular lightweight data-interchange format. Visual Studio makes it very easy to generate class / classes out of JSON. Say, we have the following JSON available:

{
  "players": {
    "player": [
      {
        "playerId": 1,
        "playerName": "Crosby",
        "gamesPlayed": 100
      },
      {
        "playerId": 2,
        "playerName": "Niedermayer",
        "gamesPlayed": 300
      }
    ]
  }
}

 

To generate C# classes out of the above JSON, copy the JSON to clipboard, in Visual Studio click Edit > Paste Special > Paste JSON as Classes.
It will generate following classes:
 
 public class Rootobject
    {
        public Players players { get; set; }
    }

    public class Players
    {
        public Player[] player { get; set; }
    }

    public class Player
    {
        public int playerId { get; set; }
        public string playerName { get; set; }
        public int gamesPlayed { get; set; }
    }
 
 
Now you can fine-tune some of the code, e.g. change Player[] to List<Player> etc.
 
This paste special option works well in Visual Studio 2015. In case if you have any older version of Visual Studio then you might want to 
add NewtonSoft JSON NuGet package to your project to make it work:
 
image

No Comments

JSON support in SQL Server 2016

SQL Server 2016 is around the corner. XML has been supported since SQL Server 2008. In SQL Server 2016 although JSON is not natively available as a data type, but, there are boat-load of functions and keywords that lets you utilize JSON data.

 

Data Type

Data type wise, JSON is represented by NVARCHAR data type. Main reason is people have been storing JSON data for quite some time now and migration will be a lot easier if data types aren’t required to be changed.

 

Querying a relational table and returning result in JSON format

If you want to query some existing table and return result in JSON format then you can use similar syntax that’s used for returning data in XML format.

SELECT col1, col2 FROM table FOR JSON [AUTO | PATH];

If you use AUTO switch, it will use default behavior for returning column names and arrays.

For having more control over names and arrays you can use PATH switch

So, essentially you are converting data to JSON format at database layer rather than at service layer.

 

Querying JSON data and returning result in a relational table format

If you already have JSON string and you want to convert it to a relational table then OPENJSON can be used, just like OPENROWSET can be used for querying a CSV / Excel file.

Let’s assume we have employees variable that contains collection of employees.

Let’s say we have employees data stored in a variable (or some table’s column) like this:

DECLARE @employees nvarchar(100)

SET @employees ={ ‘employees’:[

 {‘EmployeeId’:1,EmployeeName’:’Bob’},

 {‘EmployeeId’:2,EmployeeName’:’Alice’}

]}

 

The following query will let us query JSON data as a relational table:

 

SELECT EmployeeId, EmployeeName

FROM OPENJSON (@employees, ‘$.employees’)

WITH

(

  EmployeeId int,

  EmployeeName varchar(20)

) AS EmployeesArray

 

Other JSON related built-in functions

ISJSON(json_data)

It’s a useful function for validating JSON.

JSON_VALUE(json_data, path)

This function returns a scalar value (some specific column/property’s value) from JSON data.

e.g.

JSON_VALUE(@employees, ‘$[0].EmployeeId’) returns employee id of first record.

JSON_VALUE(@employee, ‘$.employeeId’) returns employee id of the record ASSUMING that @employee is a single record / object and not an array.

Indexing on JSON data

Since there’s no new data type for JSON data, so you can use regular indexes for your JSON based columns.

,

2 Comments

SQL Server 2016

SQL Server is evolving rapidly. Just last year we received SQL Server 2014 and 3 years ago we received SQL Server 2012 and now we are already expecting SQL Server 2016. SQL Server 2012’s main features were Always-On, and Contained databases. SQL Server 2014’s main features were In-Memory OLTP and integration with Windows Azure. Here are some of the main features of SQL Server 2016:

1. Always Encrypted

It’s a security related feature, as obvious from the name. In the past we did have TDE for encrypting the entire database which required creation of encryption keys. In Always Encrypted seems like the encryption key will reside on the application side. More will get know when the preview software gets released.

2. Support for JSON

JSON has become a power house when it comes to data format used for data transfer. XML is already supported by SQL Server as a native data type with XQuery / XPath syntax. Now JSON is getting added to SQL Server 2016 which means a lot smaller footprint, compared to XML.

3. Advanced Analytics, PolyBase, Mobile BI

Business Intelligence is getting more love from Microsoft in SQL Server 2016. PolyBase will allow folks with T-SQL skills to extract value from structured and unstructured data. Data visualization tools will be available on Windows, iOS and Android mobile devices. Exactly how the applications will look like? We will find out soon.

4. Stretch Database

Storage is getting cheaper but still there are cases where we struggle and run out of storage. Stretch database feature allows us to archive data that’s not part of day to day transactions. So we can call the important data “warm transactional data” and rest of it that we want to archive can be called “cold transactional data”. Cold transactional data can be archived to Windows Azure cloud storage. It seems like reports can still be created based of warm as well as cold transactional data.

There are several more features but above features. Waiting anxiously for the preview release which is expected sometime this summer.

, , , , , ,

No Comments