CODEWITHSUNDEEP
mysqlsqlsql-serverjson
Harsimranjeet Singh
Harsimranjeet Singh

Reputation: 524

How to use TSQL for generating Json

I'm trying to design source document for mongodb from data already existing in tabular database sqlserver/mysql.

For practicing i've prepared three table customer,product & order transaction, with schema as below

USE scratch
GO

CREATE TABLE Customer(customer_id int identity(1,1), FirstName varchar(100),LastName varchar(100));

Create TAble Product(Product_id int identity(1,1), Name varchar(100), Price numeric(18,2), Unit varchar(10));


CREATE TABLE OrderTransaction(Tr_id int identity(1,1), customer_id int, Product_id int ,Quantity int, TotalCost numeric, trdate datetime, weigh int , weighunit varchar(100));


INSERT INTO Customer(FirstName,LastName)

SELECT 'Harry','G'
UNION ALL
SELECT 'Jenne','G'
UNION ALL
SELECT 'Gill','G'
UNION ALL
SELECT 'Tom','G'
UNION ALL
SELECT 'Bai','G'



INSERT INTO Product(Name,Price, Unit )
SELECT 'Sugar',100,'kg'
UNION ALL
SELECT 'Olive Oil',500,'litre'
UNION ALL
SELECT 'Loreal Face Wash',200,NULL
UNION ALL
SELECT 'FLour',400,'kg'
UNION ALL
SELECT 'Onion',20,'kg'
UNION ALL
SELECT 'Potato',10,'kg'



INSERT INTO OrderTransaction(customer_id,Product_id,Quantity,TotalCost,trdate,weigh,weighunit)
SELECT 1,1,5,500,GETDATE()-40,5,'kg'
UNION ALL
SELECT 1,2,1,2500,GETDATE()-40,5,'litre'
UNION ALL
SELECT 1,3,5,500,GETDATE()-30,5,'kg'
UNION ALL
SELECT 1,4,1,200,GETDATE()-30,NULL,NULL
UNION ALL
SELECT 1,5,5,100,GETDATE(),5,'kg'
UNION ALL
SELECT 1,6,5,50,GETDATE(),5,'kg'

UNION ALL
SELECT 2,1,5,500,GETDATE()-40,5,'kg'
UNION ALL
SELECT 2,2,1,2500,GETDATE()-40,5,'litre'
UNION ALL
SELECT 2,3,5,500,GETDATE()-30,5,'kg'
UNION ALL
SELECT 2,4,1,200,GETDATE()-30,NULL,NULL
UNION ALL
SELECT 2,5,5,100,GETDATE(),5,'kg'
UNION ALL
SELECT 2,6,5,50,GETDATE(),5,'kg'

I've tried Json Path in sql server 2016 but didn't find any documentation to group transaction on basis of customer, it create each transaction as a document.

select a.customer_id as 'Customer.customer_id' 
,FirstName as 'Customer.FirstName'
,LastName as 'Customer.LastName'
,Tr_id as 'Customer.AllTransaction.Tr_id'
,b.Product_id as 'Customer.AllTransaction.Product_id'
,Quantity as 'Customer.AllTransaction.Quantity'
,TotalCost as 'Customer.AllTransaction.TotalCost'
,p.Name as 'Customer.AllTransaction.ProductName'


from Customer a
inner join OrderTransaction b
on a.customer_id=b.customer_id
inner join Product p
on b.Product_id=p.Product_id
FOR JSON PATH

I would like write a query to prepare json like:

[
  {
    "customer_id": 1,
    "FirstName": "Harry",
    "LastName": "G",
    "totalsales": 3850.0,
    "LastTransactionDate": "2014-12-1208: 42: 00.000Z",
    "AllTransctionList": [
      {
        "Tr_id": 1,
        "Product_id": 1,
        "Quantity": 5,
        "TotalCost": 500,
        "ProductName": "Sugar"
      },
      {
        "Tr_id": 2,
        "Product_id": 2,
        "Quantity": 1,
        "TotalCost": 2500,
        "ProductName": "Olive Oil"
      },
      {
        "Tr_id": 3,
        "Product_id": 3,
        "Quantity": 5,
        "TotalCost": 500,
        "ProductName": "Loreal Face Wash"
      },
      {
        "Tr_id": 4,
        "Product_id": 4,
        "Quantity": 1,
        "TotalCost": 200,
        "ProductName": "FLour"
      },
      {
        "Tr_id": 5,
        "Product_id": 5,
        "Quantity": 5,
        "TotalCost": 100,
        "ProductName": "Onion"
      },
      {
        "Tr_id": 6,
        "Product_id": 6,
        "Quantity": 5,
        "TotalCost": 50,
        "ProductName": "Potato"
      }
    ]
  },
  {
    "customer_id": 2,
    "FirstName": "Jenne",
    "LastName": "G",
    "totalsales": 3850.0,
    "LastTransactionDate": "2014-12-1208: 42: 00.000Z",
    "AllTransctionList": [
      {
        "Tr_id": 1,
        "Product_id": 1,
        "Quantity": 5,
        "TotalCost": 500,
        "ProductName": "Sugar"
      },
      {
        "Tr_id": 2,
        "Product_id": 2,
        "Quantity": 1,
        "TotalCost": 2500,
        "ProductName": "Olive Oil"
      },
      {
        "Tr_id": 3,
        "Product_id": 3,
        "Quantity": 5,
        "TotalCost": 500,
        "ProductName": "Loreal Face Wash"
      },
      {
        "Tr_id": 4,
        "Product_id": 4,
        "Quantity": 1,
        "TotalCost": 200,
        "ProductName": "FLour"
      },
      {
        "Tr_id": 5,
        "Product_id": 5,
        "Quantity": 5,
        "TotalCost": 100,
        "ProductName": "Onion"
      },
      {
        "Tr_id": 6,
        "Product_id": 6,
        "Quantity": 5,
        "TotalCost": 50,
        "ProductName": "Potato"
      }
    ]
  }
]

Upvotes: 2

Views: 80

Answers (1)

Peter Schneider
Peter Schneider

Reputation: 2929

To get you started you might use something like 

SELECT a.customer_id as 'customer_id' 
,FirstName as 'FirstName'
,LastName as 'LastName'
,Tr_id as 'Tr_id'
,Quantity as 'Quantity'
,TotalCost as 'TotalCost'
from Customer a 
inner join OrderTransaction AllTransactionList
on a.customer_id=AllTransactionList.customer_id
FOR JSON AUTO

Instead of joining OrderTransaction you might get your product data first or use a subselect in the select list like in this example (Query3).

Like:

USE scratch
GO




SELECT a.customer_id     AS 'customer_id' 
,FirstName               AS 'FirstName'
,LastName                AS 'LastName'
,LastTransactionDate
,totalsales
,Tr_id                   AS 'Tr_id'
,ProductName
,Quantity                AS 'Quantity'
,TotalCost               AS 'TotalCost'
FROM 
(SELECT c.customer_id 
,FirstName
,LastName 
,MAX(trdate) LastTransactionDate
,SUM(TotalCost)totalsales

FROM Customer c
INNER JOIN OrderTransaction o
ON c.customer_id=o.customer_id
GROUP BY  
c.customer_id 
,FirstName
,LastName )a

INNER JOIN 
    (SELECT 
    o.Tr_id
    ,o.customer_id
    ,p.Name AS ProductName
    ,o.Quantity
    ,o.TotalCost
    ,o.trdate
    ,o.weigh
    ,o.weighunit 
    FROM OrderTransaction o
    INNER JOIN Product p
    ON o.Product_id=p.Product_id)AllTransactionList

ON a.customer_id=AllTransactionList.customer_id

FOR JSON AUTO

Upvotes: 1

Related Questions