JavaScript – What’s the difference between using “let” and “var” to declare a variable?

[Origin]: https://stackoverflow.com/questions/762011/whats-the-difference-between-using-let-and-var-to-declare-a-variable

The difference is scoping. var is scoped to the nearest function block and let is scoped to the nearest enclosing block, which can be smaller than a function block. Both are global if outside any block.

Also, variables declared with let are not accessible before they are declared in their enclosing block. As seen in the demo, this will throw a ReferenceError exception.

Demo:

Global:

They are very similar when used like this outside a function block.

let me = 'go';  // globally scoped
var i = 'able'; // globally scoped

However, global variables defined with let will not be added as properties on the global windowobject like those defined with var.

console.log(window.me); // undefined
console.log(window.i); // 'able'

Function:

They are identical when used like this in a function block.

function ingWithinEstablishedParameters() {
  let terOfRecommendation = 'awesome worker!'; //function block scoped
  var sityCheerleading = 'go!'; //function block scoped
}

Block:

Here is the difference. let is only visible in the for() loop and var is visible to the whole function.

function allyIlliterate() {
  //tuce is *not* visible out here

  for( let tuce = 0; tuce < 5; tuce++ ) {
    //tuce is only visible in here (and in the for() parentheses)
    //and there is a separate tuce variable for each iteration of the loop
  }

  //tuce is *not* visible out here
}

function byE40() {
  //nish *is* visible out here

  for( var nish = 0; nish < 5; nish++ ) {
    //nish is visible to the whole function
  }

  //nish *is* visible out here
}

Redeclaration:

Assuming strict mode, var will let you re-declare the same variable in the same scope. On the other hand, let will not:

'use strict';
let me = 'foo';
let me = 'bar'; // SyntaxError: Identifier 'me' has already been declared

'use strict';
var me = 'foo';
var me = 'bar'; // No problem, `me` is replaced.
shareedit
Advertisements

What does the construct x = x || y mean?

[Origin]: https://stackoverflow.com/questions/2802055/what-does-the-construct-x-x-y-mean/2802064

What is double pipe operator (||)?

Double pipe operator (||) is logical OR operator . In most languages it works the following way:

  • If the first value is false, it checks the second value. If it’s true, it returns true and if it’s false, it returns false.
  • If the first value is true, it always returns true, no matter what the second value is.

So it’s basically (note that it’s actually a pseudo-code, because you have to either give this function a name or wrap it in ( and ) to make it work):

function(x, y) {
  if (x) {
    return true;
  } else if (y) {
    return true;
  } else {
    return false;
  }
}

If you still don’t understand, look at this table:

      | true   false  
------+---------------
true  | true   true   
false | true   false  

In other words, it’s only false when both values are false.

How is it different in JavaScript?

JavaScript is a bit different, because it’s a loosely typed language. In this case it means that you can use || operator with values that are not booleans. Though it makes no sense, you can use this operator with for example a function and an object:

(function(){}) || {}

What happens there?

If values are not boolean, JavaScript makes implicit conversation to boolean. It means that if the value is falsey (e.g. 0""nullundefined (see also All falsey values in JavaScript)), it will be treated as false; otherwise it’s treated as true.

So the above example should give true, because empty function is truthy. Well, it doesn’t. It returns the empty function. That’s because JavaScript’s || operator doesn’t work as I wrote at the beginning. It works the following way:

  • If the first value is falsey, it returns the second value.
  • If the first value is truthy, it returns the first value.

Surprised? Actually, it’s “compatible” with the traditional || operator. It could be written as following function:

function(x, y) {
  if (x) {
    return x;
  } else {
    return y;
  }
}

If you pass a truthy value as x, it returns x, that is, a truthy value. So if you use it later in ifclause:

(function(x, y) {
  var eitherXorY = x || y;
  if (eitherXorY) {
    console.log("Either x or y is truthy.");
  } else {
    console.log("Neither x nor y is truthy");
  }
}(true/*, undefined*/))function(x, y) {
  if (x) {
    return x;
  } else {
    return y;
  }
}

you get "Either x or y is truthy.".

If x was falsey, eitherXorY would be y. In this case you would get the "Either x or y is truthy." if y was truthy; otherwise you’d get "Neither x nor y is truthy".

The actual question

Now, when you know how || operator works, you can probably make out by yourself what does x = x || y mean. If x is truthy, x is assigned to x, so actually nothing happens; otherwise y is assigned to x. It is commonly used to define default parameters to function, because default function parameters were introduces in ES6 and are not supported by older browsers. However, it is often considered as a bad programming practice, because it prevents you from passing a falsey value (which is not necessarily undefined or null) as a parameter. Consider following example:

function badFunction(/* boolean */flagA) {
  flagA = flagA || true;
  console.log("flagA is set to " + (flagA ? "true" : "false"));
}

It looks valid at the first sight. However, what would happen if you passed false as flagAparameter (since it’s boolean, i.e. can be true or false)? It would become true. In this example, there is no way to set flagA to false.

Better example would be to explicitly check whether the flagA is undefined, like that:

function goodFunction(/* boolean */flagA) {
  flagA = typeof flagA !== "undefined" ? flagA : true;
  console.log("flagA is set to " + (flagA ? "true" : "false"));
}

Though it’s longer, it always works and it’s easier to understand (which OP has proven by asking this question).

See also

shareedit

Three steps to use jQuery UI in ASP.NET MVC 5

[Origin]: https://blog.falafel.com/three-steps-use-jquery-ui-asp-net-mvc-5/

Many developers struggle to work with jQuery UI in an ASP.NET MVC application. In this post, I will show you three steps required to start working with jQuery UI in an ASP.NET MVC application. At the end of the post we will take a look at working with the autocomplete widget.
The following three steps will let you work with jQuery UI in an ASP.NET MVC application:
Step 1: Add the jQuery UI Reference
Add the jQuery UI reference into the project using the NuGet manager. Once this is done, you should find the reference added in the Content folder and the Scripts folder.

jqueryui1

Step 2: Bundle the required files
Open the BundleConfig.cs file. In this file add two entries, one for the jQuery UI scripts and other for jQuery UI CSS.
Add the script entry as follows:

Next add the CSS files for jQueryUI widgets.  CSS for all the widgets can be bundled like this:

For the purpose of this example, let’s say you are only working with the autocomplete widget. In this case, you would only bundle the core.css and autocomplete.css as shown below:

Step 3: Refer to the Bundles
Once the bundles for jQuery UI have been created, you need to add them to the layout file. That can be done as follows:

By default you will find the jQuery bundle at the end of the layout file. To work with jQuery UI widgets, you should move the jQuery bundle to the top of the file and also include the bundles for jQuery UI CSS and Scripts.
You have now completed the three steps necessary to work with jQueryUI in an ASP.NET MVC application.
Use jQueryUI Widgets 
Now let’s look at the autocomplete widget in action.  I have created a controller for returning JSON data as follows:

We will now bind the returned JSON from the GetMP() action to the autocomplete widget. On the razor view, you can create an autocomplete widget like this:

Make sure the view is using the layout file in which you added the reference of the bundles.
Summary 
We’ve seen in this post that working with jQuery UI widgets is as simple as following three simple steps:

  1. Add the  jQuery UI reference
  2. Bundle the required files
  3. Refer to the bundles

The autocomplete widget is also a helpful tool when working with jQuery UI bundles.
I hope this post was useful. Happy coding.

How to add jQuery UI to your ASP.NET MVC App in 3 steps

[Origin]: https://omarnegronm.wordpress.com/2015/08/29/how-to-add-jquery-ui-to-your-asp-net-mvc-project-in-3-steps/

This is a quick demo on how to add jQuery UI to your MVC project in Visual Studio 2015 but the process applies to earlier versions as well. For this example, I’ll use the accordion widget.

This is how the page looks before applying the accordion widget…

img-3

and after…

img-11The index.cshtml markup:

@{
    ViewBag.Title = "Home Page";
}
 
<div id="accordion" class="prettyprint">
    <h3>Section 1</h3>
    <div>
        <p>
            Mauris mauris ante, blandit et, ultrices a, suscipit eget, quam. Integer
            ut neque. Vivamus nisi metus, molestie vel, gravida in, condimentum sit
            amet, nunc. Nam a nibh. Donec suscipit eros. Nam mi. Proin viverra leo ut
            odio. Curabitur malesuada. Vestibulum a velit eu ante scelerisque vulputate.
        </p>
    </div>
    <h3>Section 2</h3>
    <div>
        <p>
            Sed non urna. Donec et ante. Phasellus eu ligula. Vestibulum sit amet
            purus. Vivamus hendrerit, dolor at aliquet laoreet, mauris turpis porttitor
            velit, faucibus interdum tellus libero ac justo. Vivamus non quam. In
            suscipit faucibus urna.
        </p>
    </div>
    <h3>Section 3</h3>
    <div>
        <p>
            Nam enim risus, molestie et, porta ac, aliquam ac, risus. Quisque lobortis.
            Phasellus pellentesque purus in massa. Aenean in pede. Phasellus ac libero
            ac tellus pellentesque semper. Sed ac felis. Sed commodo, magna quis
            lacinia ornare, quam ante aliquam nisi, eu iaculis leo purus venenatis dui.
        </p>
        <ul>
            <li>List item one</li>
            <li>List item two</li>
            <li>List item three</li>
        </ul>
    </div>
    <h3>Section 4</h3>
    <div>
        <p>
            Cras dictum. Pellentesque habitant morbi tristique senectus et netus
            et malesuada fames ac turpis egestas. Vestibulum ante ipsum primis in
            faucibus orci luctus et ultrices posuere cubilia Curae; Aenean lacinia
            mauris vel est.
        </p>
        <p>
            Suspendisse eu nisl. Nullam ut libero. Integer dignissim consequat lectus.
            Class aptent taciti sociosqu ad litora torquent per conubia nostra, per
            inceptos himenaeos.
        </p>
    </div>
</div>
 
@section scripts
{
    <script>
        $('#accordion').accordion();
    </script>
     
}

Notice at the bottom the @scripts section. This is defined in your _Layout.cshtml view. There you will find the @RenderSection method being called as you can see below:

_Layout.cshtml

    ...
 
    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/bootstrap")
 
    @RenderSection("scripts", required: false)
</body>
</html>

1) Install jQuery.UI.Combined

jQuery is already included in your project but not jQuery-UI. Install jQuery.UI.Combined with the Nuget Package Manager. This package includes all of the jQuery-UI widgets and animations.

img-4

A quicker way of installing it is by typing the following command in the Package Manager Console:

PM> Install-Package jQuery.UI.Combined

2) Add the jQuery-UI script and style bundles

Add the script and style bundles to your BundleConfig.cs file in the App_Start folder.

using System.Web;
using System.Web.Optimization;
 
namespace jQueryDemo
{
    public class BundleConfig
    {
        public static void RegisterBundles(BundleCollection bundles)
        {
            bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
                        "~/Scripts/jquery-{version}.js"));
 
            bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
                        "~/Scripts/jquery.validate*"));
 
            // Add the jquery-ui script bundle
            bundles.Add(new ScriptBundle("~/bundles/jquery-ui").Include(
                        "~/Scripts/jquery-ui-{version}.js"));
 
            // Add the jquery-ui css bundle
            bundles.Add(new StyleBundle("~/Content/themes/base/css").Include(
                        "~/Content/themes/base/all.css"));
             
            bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
                        "~/Scripts/modernizr-*"));
 
            bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
                      "~/Scripts/bootstrap.js",
                      "~/Scripts/respond.js"));
 
            bundles.Add(new StyleBundle("~/Content/css").Include(
                      "~/Content/bootstrap.css",
                      "~/Content/site.css"));
        }
    }
}

3) Call the Script and Style Render Methods in the Layout View

Finally, call the @Scripts.Render() and @Styles.Render() methods in your _Layout.cshtml after the jQuery render method is called.

    ...
     
    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/bootstrap")
 
    <!-- Including the jquery-ui script and style bundles -->
    @Scripts.Render("~/bundles/jquery-ui")
    @Styles.Render("~/Content/themes/base/css")
 
    @RenderSection("scripts", required: false)
</body>
</html>

That’s it! Build your project, refresh the page, and enjoy the results. If you don’t include the installation, it’s basically a two-step process. Include the script and style bundles in your BundleConfig file and call the render methods from your layout view.

Applying a theme

If the base theme is too plain-looking you might want to go for one of the many different themes offered by jQuery-UI. Let’s go ahead an apply the Redmond theme to our project. Begin by installing jQuery.UI.Themes through the Nuget Package Manager. This package includes all of the jQuery themes in one download.

img-12

Once it’s installed check that the themes appear in your solution explorer in the Content > Themes folder.

img-13

Now you need to add the theme’s style bundle in your BundleConfig.cs file and remove the base theme. In our case, we’ll make sure the style bundle points to redmond/jquery-ui-redmond.css.

using System.Web;
using System.Web.Optimization;
namespace jQueryDemo
{
    public class BundleConfig
    {
        public static void RegisterBundles(BundleCollection bundles)
        {
            bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
                        "~/Scripts/jquery-{version}.js"));
            bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
                        "~/Scripts/jquery.validate*"));
            
            bundles.Add(new ScriptBundle("~/bundles/jquery-ui").Include(
                        "~/Scripts/jquery-ui-{version}.js"));
            // Removing the base theme.
            // bundles.Add(new StyleBundle("~/Content/themes/base/css").Include(
            //            "~/Content/themes/base/all.css"));
            // Adding the 'redmond' style bundle.
            bundles.Add(new StyleBundle("~/Content/themes/redmond/css").Include(
                        "~/Content/themes/redmond/jquery-ui.redmond.css"));
            bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
                        "~/Scripts/modernizr-*"));
            bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
                      "~/Scripts/bootstrap.js",
                      "~/Scripts/respond.js"));
            bundles.Add(new StyleBundle("~/Content/css").Include(
                      "~/Content/bootstrap.css",
                      "~/Content/site.css"));
        }
    }
}

Finally, head to your _Layout.cshtml and change @Styles.Render(“~/Content/themes/base/css”) to @Styles.Render(“~/Content/themes/redmond/css”)

  ...
 
    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/bootstrap")
 
    <!-- Including the jquery-ui script and style bundles -->
    @Scripts.Render("~/bundles/jquery-ui")
    @Styles.Render("~/Content/themes/redmond/css")
 
    @RenderSection("scripts", required: false)
</body>
</html>

Rebuild the project and the theme should now be active when you launch the application.

img-14

View JSON in Internet Explorer 7-11

[Origin]: http://developers.de/blogs/holger_vetter/archive/2013/12/13/view-json-in-internet-explorer-7-11.aspx

Hello guys,

you all know the problem, want to debug an application with IE and IE always wants to download the JSON file.

The other browsers like Chrome or Firefox are doing a better job here.

If you want that IE shows the JSON file without downloading it, here is a registry fix for it.

Create a new text document and insert this (Link: http://pastebin.com/B0BXG0N6):

Windows Registry Editor Version 5.00;
; Tell IE 7,8,9,10,11 to open JSON documents in the browser on Windows XP and later.
; 25336920-03F9-11cf-8FD0-00AA00686F13 is the CLSID for the "Browse in place" .
;
[HKEY_CLASSES_ROOT\MIME\Database\Content Type\application/json]
"CLSID"="{25336920-03F9-11cf-8FD0-00AA00686F13}"
"Encoding"=hex:08,00,00,00

[HKEY_CLASSES_ROOT\MIME\Database\Content Type\text/json]
"CLSID"="{25336920-03F9-11cf-8FD0-00AA00686F13}"
"Encoding"=hex:08,00,00,00

Now rename the document to {FileName}.reg and save.

Run it and confirm the dialog.

Now JSON files will be shown directly in IE, without downloading.

*Thanks to Coding 101 from http://www.codeproject.com/Tips/216175/View-JSON-in-Internet-Explorer for the Script

Debugging JavaScript Using Google Chrome

[Origin]: http://www.c-sharpcorner.com/UploadFile/3d39b4/debugging-javascript-using-google-chrome/

We are working with data and data are storing in JavaScript variables. We need to see what value is passed to the function and how the value is manipulated and what result the value is producing so we need to debug the JavaScript function to trace a variable value line by line. In the previous article I explained how to debug a JavaScript function using Visual Studio and Internet Explorer; that article is:

Debugging JavaScript Code Using VS and IE

So now we go through step-by-step to debug a JavaScript function using Google Chrome.

Create UI Design and Develop Functionality for Addition

We create a UI Design that takes two values and calculates the addition of these two values on a button click.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type ="text/javascript">
        function AddNumber()
        {
            var result = document.getElementById("<%=lblResult.ClientID %>");
            var firstNumber = document.getElementById("<%=txtFirstNumber.ClientID %>").value;
            var secondNumber = document.getElementById("<%=txtSecondNumber.ClientID %>").value;
            var addition = parseInt( firstNumber) + parseInt(secondNumber);
            result.innerHTML = "Addition is :" + addition;
            return false;
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    Enter First Number : <asp:TextBox ID="txtFirstNumber" runat="server"></asp:TextBox><br />
    Enter Second Number : <asp:TextBox ID="txtSecondNumber" runat="server"></asp:TextBox><br />
    <asp:Button ID="btnAdd" runat="server" Text="Addition" OnClientClick="javascript:return AddNumber();" /><br/>
    <asp:Label ID="lblResult" runat="server"></asp:Label>
    </div>
    </form>
</body>
</html>

Value Input Window

image1.png

Add Debugger in the JavaScript function so the cursor can hit here whenever the function is called.

image2.png

Setting and Debug In Google Chrome

Go to “Tools” then click on “JavaScript console”.

image3.png
image4.png

Run the application and insert values into the input fields then click on the “Addition” button. The cursor will then reach the debugger in the source tab under the JavaScript console window.

image5.png

In this function we declare several variables and on starting, these variables are #ff0000; check the Scope Variables panel in the Right Pane.

image6.png

Click on F10 and move forward line by line, then check the value of the variables using a mouse move on that variable.

image7.png

We can also check data in the scope variables window.

image8.png

We can also check for an error in the JavaScript code. For example we don’t define a variable and use that variable. I have a variable firstNumber and use the myfirstNumber variable to parse in an int type but instead firstNumber is undefined.

image9.png

We can insert a breakpoint by double-clicking on line number or using right-click of the mouse, then click on “Add breakpoint item”. When we use a breakpoint we don’t need to define the debugger in the JavaScript function and the cursor will hit the breakpoint directly.

image10.png

We have the Breakpoints window where the line with the breakpoint checkbox checked will be shown.

image11.png

JSON Data (SQL Server)

[Origin]: https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server

JSON is a popular textual data format used for exchanging data in modern web and mobile applications. JSON is also used for storing unstructured data in log files or NoSQL databases like Microsoft Azure DocumentDB. Many REST web services return results formatted as JSON text or accept data formatted as JSON. For example, most Azure services such as Azure Search, Azure Storage, and Azure DocumentDb have REST endpoints that return or consume JSON. JSON is also the main format for exchanging data between web pages and web servers using AJAX calls.

Here’s an example of JSON text:

[{
    "name": "John",
    "skills": ["SQL", "C#", "Azure"]
}, {
    "name": "Jane",
    "surname": "Doe"
}]

SQL Server provides built-in functions and operators that let you do the following things with JSON text.

  • Parse JSON text and read or modify values.
  • Transform arrays of JSON objects into table format.
  • Run any Transact-SQL query on the converted JSON objects.
  • Format the results of Transact-SQL queries in JSON format.

    Overview of built-in JSON support

Key JSON capabilities of SQL Server

Here’s more info about the key capabilities that SQL Server provides with its built-in JSON support.

Extract values from JSON text and use them in queries

If you have JSON text that’s stored in database tables, you can use built-in functions to read or modify values in the JSON text.

  • Use the JSON_VALUE function to extract a scalar value from a JSON string.
  • Use JSON_QUERY to extract an object or an array from a JSON string.
  • Use the ISJSON function to test whether a string contains valid JSON.
  • Use the JSON_MODIFY function to change a value in a JSON string.

Example

In the following example, the query uses both relational and JSON data (stored in the jsonCol column) from a table:

SELECT Name,Surname,
 JSON_VALUE(jsonCol,'$.info.address.PostCode') AS PostCode,
 JSON_VALUE(jsonCol,'$.info.address."Address Line 1"')+' '
  +JSON_VALUE(jsonCol,'$.info.address."Address Line 2"') AS Address,
 JSON_QUERY(jsonCol,'$.info.skills') AS Skills
FROM PeopleCollection
WHERE ISJSON(jsonCol)&gt;0
 AND JSON_VALUE(jsonCol,'$.info.address.Town')='Belgrade'
 AND Status='Active'
ORDER BY JSON_VALUE(jsonCol,'$.info.address.PostCode')

Applications and tools see no difference between the values taken from scalar table columns and the values taken from JSON columns. You can use values from JSON text in any part of a Transact-SQL query (including WHERE, ORDER BY, or GROUP BY clauses, window aggregates, and so on). JSON functions use JavaScript-like syntax for referencing values inside JSON text.

For more info, see Validate, Query, and Change JSON Data with Built-in Functions (SQL Server), JSON_VALUE (Transact-SQL), and JSON_QUERY (Transact-SQL).

Change JSON values

If you have to modify parts of JSON text, you can use the JSON_MODIFY function to update the value of a property in a JSON string and return the updated JSON string. The following example updates the value of a property in a variable that contains JSON.

DECLARE @jsonInfo NVARCHAR(MAX)

SET @jsonInfo=JSON_MODIFY(@jsonInfo,'$.info.address[0].town','London') 

Convert JSON collections to a rowset

You don’t need a custom query language to query JSON in SQL Server. To query JSON data, you can use standard T-SQL. If you have to create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. For more info, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).

The following example calls OPENJSON and transforms the array of objects stored in the @json variable to a rowset that can be queried with a standard SQL SELECT statement:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith" }, "dob": "2005-11-04T12:00:00" }  
 ]'  

SELECT *  
FROM OPENJSON(@json)  
  WITH (id int 'strict $.id',  
        firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
        age int, dateOfBirth datetime2 '$.dob')  

Results

id firstName lastName age dateOfBirth
2 John Smith 25
5 Jane Smith 2005-11-04T12:00:00

OPENJSON transforms the array of JSON objects into a table in which each object is represented as one row, and key/value pairs are returned as cells. The output observes the following rules.

  • OPENJSON converts JSON values to the types specified in the WITH clause.
  • OPENJSON can handle both flat key/value pairs and nested, hierarchically organized objects.
  • You don’t have to return all the fields contained in the JSON text.
  • OPENJSON returns NULL values if JSON values don’t exist.
  • You can optionally specify a path after the type specification to reference a nested property or to reference a property by a different name.
  • The optional strict prefix in the path specifies that values for the specified properties must exist in the JSON text.

For more info, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server) and OPENJSON (Transact-SQL).

Convert SQL Server data to JSON or export JSON

Format SQL Server data or the results of SQL queries as JSON by adding the FOR JSON clause to a SELECT statement. Use FOR JSON to delegate the formatting of JSON output from your client applications to SQL Server. For more info, see Format Query Results as JSON with FOR JSON (SQL Server).

The following example uses PATH mode with the FOR JSON clause.

SELECT id, firstName AS "info.name", lastName AS "info.surname", age, dateOfBirth as dob  
FROM People  
FOR JSON PATH  

The FOR JSON clause formats SQL results as JSON text that can be provided to any app that understands JSON. The PATH option uses dot-separated aliases in the SELECT clause to nest objects in the query results.

Results

[{
    "id": 2,
    "info": {
        "name": "John",
        "surname": "Smith"
    },
    "age": 25
}, {
    "id": 5,
    "info": {
        "name": "Jane",
        "surname": "Smith"
    },
    "dob": "2005-11-04T12:00:00"
}] 

For more info, see Format Query Results as JSON with FOR JSON (SQL Server) and FOR Clause (Transact-SQL).

Combine relational and JSON data

SQL Server provides a hybrid model for storing and processing both relational and JSON data using standard Transact-SQL language. You can organize collections of your JSON documents in tables, establish relationships between them, combine strongly-typed scalar columns stored in tables with flexible key/value pairs stored in JSON columns, and query both scalar and JSON values in one or more tables using full Transact-SQL.

JSON text is typically stored in varchar or nvarchar columns and is indexed as plain text. Any SQL Server feature or component that supports text supports JSON, so there are almost no constraints on interaction between JSON and other SQL Server features. You can store JSON in In-memory or Temporal tables, you can apply Row-Level Security predicates on JSON text, and so on.

If you have pure JSON workloads where you want to use a query language that’s customized for the processing of JSON documents, consider Microsoft Azure DocumentDB.

Here are some use cases that show how you can use the built-in JSON support in SQL Server.

Return data from a SQL Server table formatted as JSON

If you have a web service that takes data from the database layer and returns it in JSON format, or JavaScript frameworks or libraries that accept data formatted as JSON, you can format JSON output directly in a SQL query. Instead of writing code or including a library to convert tabular query results and then serialize objects to JSON format, you can use FOR JSON to delegate the JSON formatting to SQL Server.

For example, you might want to generate JSON output that’s compliant with the OData specification. The web service expects a request and response in the following format.

  • Request: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName
  • Response: {"@odata.context":"http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity","ProductID":1,"ProductName":"Chai"}

    This OData URL represents a request for the ProductID and ProductName columns for the product with id 1. You can use FOR JSON to format the output as expected in SQL Server.

SELECT 'http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity'
 AS '@odata.context',   
 ProductID, Name as ProductName   
FROM Production.Product  
WHERE ProductID = 1  
FOR JSON AUTO  

The output of this query is JSON text that’s fully compliant with OData spec. Formatting and escaping are handled by SQL Server. SQL Server can also format query results in any format such as OData JSON or GeoJSON – for more info, see Returning spatial data in GeoJSON format.

Analyze JSON data with SQL queries

If you have to filter or aggregate JSON data for reporting purposes, you can use OPENJSON to transform JSON to relational format. Then use standard Transact-SQL and built-in functions to prepare the reports.

SELECT Tab.Id, SalesOrderJsonData.Customer, SalesOrderJsonData.Date  
FROM   SalesOrderRecord AS Tab  
          CROSS APPLY  
     OPENJSON (Tab.json, N'$.Orders.OrdersArray')  
           WITH (  
              Number   varchar(200) N'$.Order.Number',   
              Date     datetime     N'$.Order.Date',  
              Customer varchar(200) N'$.AccountNumber',   
              Quantity int          N'$.Item.Quantity'  
           )  
  AS SalesOrderJsonData  
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'  
ORDER BY JSON_VALUE(Tab.json, '$.Group'), Tab.DateModified  

Both standard table columns and values from JSON text can be used in the same query. You can add indexes on the JSON_VALUE(Tab.json, '$.Status') expression to improve performance of query. For more info, see Index JSON data.

Import JSON data into SQL Server tables

If you have to load JSON data from an external service into SQL Server, you can use OPENJSON to import the data into SQL Server instead of parsing the data in the application layer.

DECLARE @jsonVariable NVARCHAR(MAX)

SET @jsonVariable = N'[  
        {  
          "Order": {  
            "Number":"SO43659",  
            "Date":"2011-05-31T00:00:00"  
          },  
          "AccountNumber":"AW29825",  
          "Item": {  
            "Price":2024.9940,  
            "Quantity":1  
          }  
        },  
        {  
          "Order": {  
            "Number":"SO43661",  
            "Date":"2011-06-01T00:00:00"  
          },  
          "AccountNumber":"AW73565",  
          "Item": {  
            "Price":2024.9940,  
            "Quantity":3  
          }  
       }  
  ]'

INSERT INTO SalesReport  
SELECT SalesOrderJsonData.*  
FROM OPENJSON (@jsonVariable, N'$.Orders.OrdersArray')  
           WITH (  
              Number   varchar(200) N'$.Order.Number',   
              Date     datetime     N'$.Order.Date',  
              Customer varchar(200) N'$.AccountNumber',   
              Quantity int          N'$.Item.Quantity'  
           )  
  AS SalesOrderJsonData;

The content of the JSON variable can be provided by an external REST service, sent as a parameter from a client-side JavaScript framework, or loaded from external files. You can easily insert, update or merge results from JSON text into a SQL Server table. For more info about this scenario, see the following blog posts.

Load JSON files into SQL Server

Information stored in files can be formatted as standard JSON or Line-Delimited JSON. SQL Server can import the contents of JSON files, parse it using the OPENJSON or JSON_VALUE functions, and load it into tables.

  • If your JSON documents are stored in local files, on shared network drives, or in Azure File Storage locations that can be accessed by SQL Server, you can use bulk import to load your JSON data into SQL Server. For more info about this scenario, see Importing JSON files into SQL Server using OPENROWSET (BULK).
  • If your line-delimited JSON files are stored in Azure Blob Storage or the Hadoop file system, you can use Polybase to load JSON text, parse it in Transact-SQL code, and load it into tables.

Test drive built-in JSON support

Test drive built-in JSON support with the AdventureWorks sample database. To get the AdventureWorks sample database, download at least the database file and the samples and scripts file from here. After you restore the sample database to an instance of SQL Server 2016, unzip the samples file and open the “JSON Sample Queries procedures views and indexes.sql” file from the JSON folder. Run the scripts in this file to reformat some existing data as JSON data, run sample queries and reports over the JSON data, index the JSON data, and import and export JSON.

Here’s what you can do with the scripts included in the file.

  1. Denormalize the existing schema to create columns of JSON data.
    1. Store information from SalesReasons, SalesOrderDetails, SalesPerson, Customer, and other tables that contain information related to sales order into JSON columns in the SalesOrder_json table.
    2. Store information from EmailAddresses/PersonPhone tables into the Person_json table as arrays of JSON objects.
  2. Create procedures and views that query JSON data.
  3. Index JSON data – create indexes on JSON properties and full-text indexes.
  4. Import and export JSON – create and run procedures that export the content of the Person and the SalesOrder tables as JSON results, and import and update the Person and the SalesOrder tables using JSON input.
  5. Run query examples – run some queries that call the stored procedures and views created in steps 2 and 4.
  6. Clean up scripts – don’t run this part if you want to keep the stored procedures and views created in steps 2 and 4.

Learn more about built-in JSON support

Topics in this section

Format Query Results as JSON with FOR JSON (SQL Server)
Use the FOR JSON clause to delegate the formatting of JSON output from your client applications to SQL Server.

Convert JSON Data to Rows and Columns with OPENJSON (SQL Server)
Use OPENJSON to import JSON data into SQL Server, or to convert JSON data into relational format for an app or service that can’t currently consume JSON directly, such as SQL Server Integration Services.

Validate, Query, and Change JSON Data with Built-in Functions (SQL Server)
Use these built-in functions to validate JSON text and to extract a scalar value, an object, or an array.

JSON Path Expressions (SQL Server)
Use a path expression to specify the JSON text that you want to use.

Index JSON data
Use computed columns to create collation-aware indexes over properties in JSON documents.

Solve common issues with JSON in SQL Server
Find answers to some common questions about the built-in JSON support in SQL Server.

Microsoft blog posts

Reference topics