for-json-sql-query.sql

 
SELECT 
		(
		select 
			TicketMasters.Id as _id,
			dbo.ParseText(TicketMasters.TicketNo) as ticketNo,
			dbo.ParseDate(TicketMasters.StartTS) as  'timeStamp.$date',
			TicketMasters.Remarks as remarks,
			(
				SELECT 
					dbo.ParseText(TicketDetails.[Id])   as '_id',
					dbo.ParseText(TicketDetails.[ProducerId]) as 'producerId',
					TicketDetails.[BarCode] as 'code',
					dbo.ParseText(TicketDetails.[GradeTypeId]) as  'gradeTypeId',
					TicketDetails.Pounds as  'weight',
					dbo.ParseDate(TicketDetails.PickupTS) as  'timeStamp.$date',
					TicketDetails.GaugeRod as 'gaugeRod',
					TicketDetails.Temp as 'temperature',
					TicketDetails.Notes as 'remarks'
				FROM dbo.TicketDetails where TicketDetails.TicketMasterId=TicketMasters.Id
				FOR JSON PATH
			) as 'details',
			(
				SELECT 
					dbo.ParseText(TicketDeliveries.[Id])   as '_id',
					TicketDeliveries.Pounds as 'weight',
					dbo.ParseDate(TicketDeliveries.DeliveryTS) as  'timeStamp.$date',
					TicketDeliveries.PlantId as 'plantId',
					TicketDeliveries.PlantBtuId as 'plantBtuId',
					TicketDeliveries.PlantSiloId as 'plantSiloId'
				FROM dbo.TicketDeliveries where TicketDeliveries.TicketMasterId=TicketMasters.Id
				FOR JSON PATH
			) as 'deliveries'
		from dbo.TicketMasters where   TicketMasters.Id = TM.Id
		--where TicketMasters.Id=207292
		FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
		) as mongoCode

from dbo.TicketMasters as TM