datamodel.txt

input
=====
{
	"order":{
			"columns":	[
					"OrderDate",
					"IdOrder",
					"ShipName",
					{"customer":{
						"columns":[
							"CompanyName",
							"ContactName",
							"ContactName"
						],
						"type": "SELECT",
						"from": "Customer"
						"as": "a",
						"where": "a.IdCustomer=b.IdCustomer",
						"arity": "1"
					}},
					{"lines":{
						"columns":[
							"UnitPrice",
							"Quantity",
							{"product":{
								"columns":[
									"ProductName",
									"QuantityPerUnit"
								],
								"type": "SELECT",
								"from": "Product"
								"as": "d",
								"where": "d.IdProduct = c.IdProduct",
								"arity":"1"
							}}
						],
						"type": "SELECT",
						"from": "OrderDetail
						"as": "c",
						"where": "c.IdOrder=b.IdOrder",
						"arity": "n"
					}}
				],
			"type": "SELECT",
			"from": "Order"
			"as":"b",
			"where": "IdOrder=10248",
			"arity": "1"
	}
}
generated SQL query
===================
SELECT '{' ||
(
  SELECT '"order":{"OrderDate":"'||"OrderDate" || '","IdOrder":"' || "IdOrder" || '","ShipName":"' || "ShipName" || '","customer":' ||
  (
    SELECT '{ "CompanyName":"' || CompanyName || '","ContactName":"' || ContactName || '"}' FROM Customer AS a WHERE a.IdCustomer=b.IdCustomer
  ) 
  || ',"lines":[' || 
  (
    SELECT  GROUP_CONCAT("record",',') ||']' FROM(
          SELECT '{"UnitPrice":'||UnitPrice || ',"Quantity":' || Quantity||',"product":{' || (
               SELECT '"ProductName":"' || "ProductName" || '"' || ',"QuantityPerUnit":"' || "QuantityPerUnit" || '"'
               FROM Product as d WHERE d.IdProduct = c.IdProduct
          )||'}}' AS record
          FROM OrderDetail AS c WHERE c.IdOrder=b.IdOrder
    )
  )
) ||  '} }'AS invoice
FROM "Order" AS b
WHERE IdOrder=10248


output
======
{  
   "order":{  
	   "OrderDate":"1996-07-04",
	   "IdOrder":"10248",
	   "ShipName":"Vins et alcools Chevalier"
	   "customer":{  
		  "CompanyName":"Vins et alcools Chevalier",
		  "ContactName":"Paul Henriot"
	   },
	   "lines":[  
		  {  
			 "UnitPrice":14,
			 "Quantity":12,
			 "product":{  
				"ProductName":"Queso Cabrales",
				"QuantityPerUnit":"1 kg pkg."
			 }
		  },
		  {  
			 "UnitPrice":9.8,
			 "Quantity":10,
			 "product":{  
				"ProductName":"Singaporean Hokkien Fried Mee",
				"QuantityPerUnit":"32 - 1 kg pkgs."
			 }
		  },
		  {  
			 "UnitPrice":34.8,
			 "Quantity":5,
			 "product":{  
				"ProductName":"Mozzarella di Giovanni",
				"QuantityPerUnit":"24 - 200 g pkgs."
			 }
		  }
	   ]
   }
}

Original idea
=============
{"invoice":
  [
	"Order":{	
				"table":"Order",
				"columns":["OrderDate","ShipDate","ShipName","ShipAddress","ShipCity","Country"],
				"where":"Order.IdOrder={{OrderNumber}}",
				"id":"IdOrder"
			},
	"Customer":{
				"table":"Customer",
				"columns":["CompanyName","ContactName","Address","City","PostalCode","Country","Phone"],
				"where":"Order.IdCustomer=Customer.IdCustomer",
				"id":"IdCustomer"
			},
	"OrderDetail":{
					"table":"OrderDetail",
					"columns":[
								"UnitPrice",
								"Quantity",
								"Discount",
								{
									"Product":{
												"table":"Product",
												"columns":["ProductName","QuantityPerUnit"],
												"where":"Product.IdProduct=OrderDetail.IdProduct"},
												"id":"idProduct"
												}
							  ],
				    "where":"Order.IdOrder=OrderDetail.IdOrder","id":"idOrderDetail"
				  }
  ]
}