Converting SQL query result into JSON string

-
SQL doesn't provide native support to translate query result into JSON format. But we can achive this with a little twisting with SQL :).

Assume we have a "Computer" table in database with the following data:

Now, we can get this data into JSON format using the query below:

Here, I've used UNPIVOT operation to de-normalized the data and then constructed the JSON string. Notice that: the ProductCode has been used as the primary key of the objects in this query.

You can also achive the same thing without using UNPIVOT; but in that case you have to construct the JSON string for every column you need by yourself.

And if you are looking for more generic one to convert each row from a data set regardless of any primary key or without knowing the exact columns to be converted, following stored procedure could be helpful:

I hope this will be helpful to someone looking for generating JSON feed directly from SQL. Cheers!

Comments

Post a Comment

Popular posts from this blog

Adding security headers to prevent XSS and MiTM attacks in .Net Core

-
Image
As a developer, we need to consider security when designing and building web applications. HTTP Response Headers allow server to pass additional information to instruct browsers how to handle sensitive data and content of the application and/or from external or untrusted sources. HTTP response security headers provide an extra layer of protection to help mitigating vulnerabilities and attacks. One way to add those security headers from .Net Core application is by writing a custom action filter that would be executed before serving any response from the application. Below is a example of the custom attribute: To apply this globally for all responses, we can add this to the Startup class as in below: To apply trusted source for any particular action: And here is the outcome: HAPPY CODING   👌
Read more

Creating transformations for custom config files

-
Image
Visual Studio provides native support to add application config transforms. But it is restricted to the native configuration files only. Also by default it only provides transforms for debug and release environment. If you need to use your own custom configuration file for custom defined environment, below are two options you can follow: Option 1: Do it "manually" Let's assume, we have a configuration file: Configuration.xml in a project. We want to add Configuration.Staging.xml and Configuration.Production.xml as transforms. To achieve this, I can add those xml files manually first and then edit the project file (.csproj) as below and then re-load the project: <ItemGroup>     <Content Include =" Configuration.xml " >        <SubType> Designer </SubType>        <TransformOnBuild> true </TransformOnBuild>        ...
Read more

Microsoft.IdentityModel.Protocols.OpenIdConnectProtocolInvalidNonceException

-
I was using openId connect authentication with IdentityServer (v4) in a recent project and my client application was throwing following error: Microsoft.IdentityModel.Protocols.OpenIdConnectProtocolInvalidNonceException: IDX10311: RequireNonce is 'true' (default) but validationContext.Nonce is null. A nonce cannot be validated. If you don't need to check the nonce, set OpenIdConnectProtocolValidator.RequireNonce to 'false'. at Microsoft.IdentityModel.Protocols.OpenIdConnectProtocolValidator.ValidateNonce(JwtSecurityToken jwt, OpenIdConnectProtocolValidationContext validationContext) in c:\workspace\WilsonForDotNet45Release\src\Microsoft.IdentityModel.Protocol.Extensions\OpenIdConnectProtocolValidator.cs:line 332 at Microsoft.IdentityModel.Protocols.OpenIdConnectProtocolValidator.Validate(JwtSecurityToken jwt, OpenIdConnectProtocolValidationContext validationContext) in c:\workspace\WilsonForDotNet45Release\src\Microsoft.IdentityModel.Protocol.Extensions\OpenIdCo...
Read more