Parsing a nested JSON object using PowerShell

Parsing a JSON object using PowerShell can give quick and fast results without too much coding and navigating objects and this is especially true in the case of nested JSON objects.

For example consider this example:
{
  "name": "donut",
  "image":
    {
    "fname": "donut.jpg",
    "w": 200,
    "h": 200
    },
  "thumbnail":
    {
    "fname": "donutThumb.jpg",
    "w": 32,
    "h": 32
    }
}
In order to parse this using PowerShell you formulate this in a Here string and use the ConverstFrom-JSON cmdlet. The rest is easy as you see in this:

JSONNested_01

Compare with this method using a stored procedure in SQL Server.

This next one appeared in StackOverflow.com. The question was, how to get to the value item of slots (which is req1) in the following JSON formmated string.

{"request": {
  "locale": "en-US",
  "timestamp": "2016-09-25T00:36:14Z",
  "type": {
    "name": "request",
    "slots": {
      "RequestTypeItem": {
        "name": "RequestTypeItem",
        "value": "req1"
      }
    }
  }
}
}

One of the answers using JavaScript can be found here. It is correct and gets you to the value as 'req1'.

However, PowerShell provides quite an elegant answer using the ConvertFrom-JSON cmdlet as shown here:

NestedJson_2.png

Read many more related PowerShell posts here.

Stored Procedure using Common Table Expression

You can use the Common Table Expression (CTE) to create a Stored procedure. While CTE is only in memory the Stored Procedure is persisted.
In order to create a stored procedure I will be using the same Common Table Expression used in my two earlier posts here and here.

Run the following in the query pane of SQL Server Management Studio (herein SSMS 2012).
----
USE Northwind
GO
CREATE PROCEDURE dbo.GetName
@city varchar(10)
AS
WITH NCTE(FirstName, LastName,City)
  as

 (SELECT FirstName,LastName,City
 FROM Employees
 )
SELECT *
FROM  NCTE
WHERE city=@city
-----
After the statement is run you will find the stored procedure as shown (you may have to refresh the stored procedures node in the Object Explorer:

---
CTEStoredProc_00

In order to run the stored procedure execute the following statement in the query pane:
Exec GetName 'London'

You get the following query response:

CTEStoredProc_01

Create a view using Common Table Expression

You can use CTE to create a view. Although CTE is in memory, the View will be persisted and can be found in the VIEWS folder in Object Explorer.

I will use the same CTE that I used in the previous post to create the view.
Run this script in the query pane of SQL Server Management Studio (herein SQL Server Developer 2012).

USE Northwind
Go
---define common table expression
Create view CityFolk
as
WITH NEmp_CTE (FirstName,LastName,CITY)  /*Name: Nemp_CTE Column List:FirstName, LastName,City*/
AS
---define the CTE query
(SELECT FirstName,LastName,City
from Employees
where City in ('Redmond','London')
)
Select * from NEmp_CTE
go

You will get the 'Command(s) completed successfully response.

Now you can find the view CityFolk in the VIEWS folder as shown.

CTEView

Common Table Expression (CTE) simply explained

Microsoft documentation defines Common Table Expression thus,

"A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

It is unlike a temporary table (persisted) during a connection or a view persisted in the database. CTE is only in memory.

We will consider CTE's many use case scenarios in later posts and the objective of this post is to give a simple example using the Northwind database.

A CTE should have the following:

• CTE with a CTE Expression name
• An Optional Column list
• A query defining the CTE

After a CTE is defined it can be referenced like a table or view and you can do all of the following statements:

• Select
• Insert
• Update
• Delete

It can also be used in defining a view by providing the columns.

Now let me define CTE using the Employees table in Northwind. The example is just to show the syntax as the result can be obtained from a single query without using CTE.

USE Northwind
Go
---define the common table expression
WITH NEmp_CTE (FirstName,LastName,CITY)  /*Name: Nemp_CTE, Column List:FirstName, LastName,City*/
AS
---define the CTE query
(SELECT FirstName,LastName,City
from Employees
where City in ('Tacoma','London')
)
---Use it in a Select Query
SELECT LastName,FirstName,CITY
From NEmp_CTE
where LastName <>'Buchanan'
go

The response to this query is:

Note: The response such as the above does not really need a Common Table Expression but the point was to show the construction of the Syntax used for CTE. In fact, it is to make complex queries more readable.

Learn Querying SQL Server 2012 using LinqPad - Part 4 Stored Procedures_2

In Learn Querying SQL Server 2012 using LinqPad - Part 3 we considered a stored procedure, albeit simple which did not have any output parameters.  LinqPad can also be used for evaluating stored procedures with an out put parameter. Make sure you review Parts 1, 2, and 3.

Let us modify the stored procedure in Part 3 so that the FullName of a Person is returned from the stored procedure as shown here.

Create Procedure PPFull
@fname nvarchar(10),
@lname nvarchar(20),
@fullname nvarchar(25) OUTPUT

as

Select @fullname=FirstName+','+Lastname from Person.person
Where FirstName=@fname and LastName=@lname
go

Note that @fullname varible is declared as an OUTPUT Parameter.

This can be executed in SQL Server Management Studio as shown.

 

The same can be executed in LinqPad as shown here.