Transform rows or table records into JSON documents within SQL Server

Here is some sample input data for your reference:

if object_id('tempdb.dbo.#store_data') is not null
drop table #store_data

create table #store_data ([key] nvarchar(max),[value] nvarchar(max), storeid varchar(100)
)

INSERT INTO #store_data VALUES ('sid','1','1')
INSERT INTO #store_data VALUES ('bid','3','1');
INSERT INTO #store_data VALUES ('time','2019-01-01','1');
INSERT INTO #store_data VALUES ('que','apple','1');
INSERT INTO #store_data VALUES ('sid','2','2');
INSERT INTO #store_data VALUES ('bid','5','2');
INSERT INTO #store_data VALUES ('hrs','6','2');
INSERT INTO #store_data VALUES ('dat','pine','2');

select * from #store_data

Below is the desired output result:

[{
"sid"="1",
"bid"="3",
"time"="2019-01-01"
"que"="apple"},
{"sid"="2",
"bid"="5",
"hrs"="6",
"dat"="pine"
}]

Here is the query that was attempted:

select [key],[value] from #store_data for json path

The results did not match the expected outcome.

sqljsonsql-servert-sqlsql-server-2017

Answer №1

You must remember that you cannot retrieve data directly through a query; you must first apply the PIVOT function on the storeid.

Here is an example query that you can try:

select sid, bid, time,que, hrs,dat 
from #store_data src
pivot
(
  MAX([value])
  for [key] in (sid, bid, time,que, hrs,dat)
) piv
for json auto

Complete Example:

 if object_id('tempdb.dbo.#store_data') is not null
 drop table #store_data

create table #store_data ([key] nvarchar(max),[value] nvarchar(max),storeid varchar(100))

INSERT INTO #store_data VALUES ('sid','1','1')
INSERT INTO #store_data VALUES ('bid','3','1');
INSERT INTO #store_data VALUES ('time','2019-01-01','1');
INSERT INTO #store_data VALUES ('que','apple','1');
INSERT INTO #store_data VALUES ('sid','2','2');
INSERT INTO #store_data VALUES ('bid','5','2');
INSERT INTO #store_data VALUES ('hrs','6','2');
INSERT INTO #store_data VALUES ('dat','pine','2');

select sid, bid, time,que, hrs,dat 
from #store_data src
pivot
(
  MAX([value])
  for [key] in (sid, bid, time,que, hrs,dat)
) piv
for json auto

Output:

[
  {
    "sid": "1",
    "bid": "3",
    "time": "2019-01-01",
    "que": "apple"
  },
  {
    "sid": "2",
    "bid": "5",
    "hrs": "6",
    "dat": "pine"
  }
]

See Online Demo:

View Here

Update:

The PIVOT automatically sorts by stroeid in ascending order without any specific ordering. If you want to change it to descending order, you can modify the query as shown below:

select sid, bid, time,que, hrs,dat 
from #store_data src
pivot
(
  MAX([value])
  for [key] in (sid, bid, time,que, hrs,dat)
) piv
order by storeid desc
for json auto

Answer №2

Here is a sample query you can try:

drop table if exists #store_data;

create table #store_data ([key] nvarchar(max),[value] nvarchar(max),storeid varchar(100)
)

INSERT INTO #store_data VALUES ('sid','1','1')
INSERT INTO #store_data VALUES ('bid','3','1');
INSERT INTO #store_data VALUES ('time','2019-01-01','1');
INSERT INTO #store_data VALUES ('que','apple','1');
INSERT INTO #store_data VALUES ('sid','2','2');
INSERT INTO #store_data VALUES ('bid','5','2');
INSERT INTO #store_data VALUES ('hrs','6','2');
INSERT INTO #store_data VALUES ('dat','pine','2');

DECLARE @DynamicTSQLStatement NVARCHAR(MAX)
       ,@ColumnNames NVARCHAR(MAX);

SELECT @ColumnNames = STUFF
(
    (
        SELECT DISTINCT ',[' + [key] + ']'
        FROM #store_data
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
   ,1
   ,1
   ,''
);

SET @DynamicTSQLStatement = N'
select ' + @ColumnNames + '
from #store_data
PIVOT 
(
    MAX([value]) FOR [key] IN (' + @ColumnNames + ')
) PVT
FOR JSON PATH;
';


EXEC sp_executesql @DynamicTSQLStatement;

https://i.stack.imgur.com/arNoE.png

To include the storeid in the JSON object, change to SELECT *. Additionally, for SQL Server 2017 users, consider using STRING_AGG to concatenate keys.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Using Bloodhound with a JSON generated using Flask's jsonify function is a seamless process

Recently, I have been exploring the Bloodhound typeahead feature to implement database search functionality in my Flask application. I found a helpful guide on how to set this up at: Twiiter Typeahead Custom Templates - Getting Default Example Working $(d ...

jsonflasksqlalchemytypeaheadbloodhound

Validate user with passport-local against a static JSON dataset

I am looking to authenticate users from a JSON file using passport-local in Node.js. Previously, I successfully used MongoDB as the user data storage solution. Now, I am hoping to experiment with using a JSON file for storing user details and having passpo ...

jsonnode.jspassport-local

How to dynamically generate data with exceljs

Currently, I am attempting to download JSON data into an Excel spreadsheet. One of my requirements is to insert an image in cell a1 and then bold the headers. The code snippet below was sourced from Google; however, I need to populate the data dynamically ...

javascriptjsonexcelnpmexceljs

Showing formatted JSON in the view using ASP.NET MVC

Is there a method to modify JSON for presentation in the interface? I am looking for a way to automatically update my API documentation when adding new properties. It would be great if I could also apply CSS styling to certain elements. This feature is som ...

cssxmlasp.net-mvcjsonrazor

Increasing Nested Values in MongoDB Using NodeJS

Looking at this JSON structure: JSON = { "change_number": "CRQ91320s23", "change_description": "Nexusg work", "date": "2/10/2020", "changeowner" : "Jeff ...

node.jsjsonmongodb

Troubleshooting problems with displaying views due to asynchronous $http.get calls in AngularJS

Within my application, I am utilizing two separate API calls. The initial call retrieves a catalog of services along with their unique ID's. Subsequently, once the ID information is acquired, a second call is made to retrieve pricing data for each cor ...

javascriptangularjsjson

Navigating through nested JSON Objects for dropdown functionality in Angular 6 - a step-by-step guide

Currently, I am facing a challenge in Angular 6.0 where I am trying to use HttpClient to iterate through JSON data retrieved from a local file within my assets folder. Below is the sample JSON Data: [{ "configKey": [{ "user1": [{ ...

jsonangulartypescriptnested

Utilizing WireMock to simulate HTTP server responses. Error finding file with WireMock

Recently began using wiremock and encountered a situation where I need to mock a GET request with a specific json response. When including the json in the expected response like this; .withBodyFile("product.json")) I'm getting an error saying java. ...

javajsonmockingwiremock

Exploring Paths in a JSON Structure using Scala and a List

When working with Play Scala, we can navigate through properties in an object using the \ method: val name = (json \ "user" \ "name") If our path is defined in a list, how can we navigate to the node? val path = List("user","name") val n ...

jsonscalaplayframework

Navigating a JSON dictionary using Python's programmatic approach

In my code, I am attempting to assign a value from a dictionary that has been formatted in JSON to a variable. Here is the JSON-formatted dictionary: "Monetarios": [{"MIFID_NO_CURR_RISK":"B1"},{"MIFID_CURR_RISK":"B2"}], "Monetario Dinamico": [{ ...

pythonjsondictionary

Tips for displaying JSON in C# using Nancyframework

I am facing an issue with displaying data from a JSON file named file.json on my screen when I access the URL localhost:8080/data. The attempted solution resulted in an error being displayed on the webpage as shown below: System.Collections.Generic.List` ...

c#jsonnancy

Calculate the sum of values in a JSON array response

I recently received a JSON string as part of an API response, and it has the following structure: { "legend_size": 1, "data": { "series": [ "2013-05-01", "2013-05-02" ], "values": { "Sign Up": { "2013-05-05": 10, ...

javascriptarraysjsonobjectaddition

No content appearing instead of AngularJS code displayed

My goal is to retrieve data from a MySQL database using PHP and then pass that data in JSON format to AngularJS for display in a table. The HTML code looks like this: <body ng-app="myModule"> <div class="row"> <div class="col-lg-12 ...

javascriptphpmysqlangularjsjson

How can I use Ajax code to send data to a PHP page and receive the results as a JSON-encoded multidimensional array containing information on each item?

Apologies for the unconventional question title. What I am trying to accomplish is managing two tables in my database: a hotel table (table1) and a room type table (table2). My goal is to allow customers to customize their travel packages by changing hote ...

javascriptphpjqueryjsonajax

Steps to establish a connection between PHP and Microsoft SQL Server

I am currently using a dedicated CentOS server that needs to establish a connection with a remote Windows machine running SQL Server 2012. The provider has installed Freetds, and my PHP version is 5.3.28. We are able to ping the remote MS-SQL server from ...

phpsql-serverpdo

Adding an object in Laravel relationships can be achieved by utilizing the relationship methods provided

How can I include additional objects in a Laravel relationship when fetching data? This is my code snippet: $list = new self; $list = $list->where('uuid', $uuid); $list = $list->where('user_id', $user->id)->orWhere('to ...

phpmysqljsonlaraveleloquent

Getting the error while extracting JSON data using Await.result() with an infinite Duration

Recently, while working with the Scala-Play framework, I encountered an error when trying to fetch data from external websites. The line of code in question is Await.result(xxx, Duration.Inf).json, which resulted in a JsonParseException: Unexpected charact ...

jsonscala

Creating dynamic email content with Node.js using SendGrid templating

How can I properly format SendGrid's content using Node.js? I'm currently working on sending emails from a contact form within an application using SendGrid. I have a Google Cloud Function set up that I call via an HTTP post request. Although I ...

javascriptjsonnode.jsecmascript-6sendgrid

What is the best way to craft an if/else statement for a situation when a twig variable is undefined?

When utilizing twig to declare a variable called user: <script type="text/javascript> {% if user is defined %} var user = { example: {{ userjson | raw }} }; {% endif %} </script> If a user is not logged in, an error message a ...

javascriptjsontwig

Having trouble decoding a potentially JSON string in Laravel controller without throwing any exceptions

After printing the results of \Input:all() in the laravel.log, I noticed the following output: Input : {"val":{"postID":"22","tags":["3"],"forwardComment":"aaaaaaa"}} [] It appears to be JSON format, so I attempted to decode it using json_d ...

phpjsonlaravel