Extract all items in a ticket to a Single JSON

You’ll have to upgrade SQL Server. If you’re using Windows 7 your SOL.

Your only other option would be to process everything outside the db.

function posm(tid)
{
var datafields = new Array();
  datafields.push("TicketId");
  datafields.push("MenuItemId");
  datafields.push("MenuItemName");
  datafields.push("Price");
  datafields.push("Quantity");


  var datafieldscount = datafields.length;
  
  var qry = "";

  qry += "SELECT";
  
  for (var d=0; d < datafieldscount; d++)
  {
    qry += (d>0 ? " ," : " ");
    qry += "[" + datafields[d] + "]";
  }
  
  qry += " FROM [Orders]";

  qry += " WHERE 1=1";
  qry += " AND [TicketId] = " + tid;

  
  var qryresult = sql.Query(qry).Delimit('~').First;
  var qrydata = qryresult.split('~');
  var qryfieldcount = qrydata.length;  

  var data = new Object();
  
  for (var d=0; d < datafieldscount; d++)
  {
    data[datafields[d]] = qrydata[d];
  }
  
  var jsondata = JSON.stringify(data);

 return jsondata;
  
}

@Posflow I have tried that function but itonly returns the first line only yet the are four lines


This would be a good scenario for using JSON PATH.

This will return a JSON array of the orders:

function posm(tid) {
    var queryString = "SELECT TicketId, MenuItemId, MenuItemName, Price, Quantity " +
                          "FROM dbo.Orders WHERE TicketId = " + tid + " FOR JSON PATH;";
    
    return sql.Query(queryString).First;
}

thank you alot
i worked worked

i will post the full script when am done

1 Like