Smarter JSON from SQL

REST API
1

Hi,

I’m wondering… is there a way you can write your SQL Stored Procedures to return a smarter or more tidy JSON object rather than just a mass of columns? For example I have one SP which returns quote and customer information but I would love it if it could come back with the quote info in one property and the customer in another without having to call two SPs and setting the data manually. e.g. …

{
quote: column column column,
customer: column column column
}

May seem petty but just wondering…

Cheers

2

Yes, you just need to create a procedure in your DB, then you call via GET this way:

/your-db-service/_proc/{procedure_name}

3

This is yes calling a stored procedure, but my question is more around is there a way to write your procedure that would return the data in separate properties without having to call multiple procedures.

So rather than this coming back…

{
quotecolumn1, quotecolumn2, quotecolumn3, customercolumn1, customercolumn2, customercolumn3
}

if could come back
{
quote: quotecolumn1, quotecolumn2, quotecolumn3,
customer: customercolumn1, customercolumn2, customercolumn3
}

Cheers

4

Ah sorry,

Ok, one way is creating a Custom Script that call those two SP and return a JSON with those two SP grouped as result.

Ex code:

quotes = platform.api.get('db-service/_proc/sp_get_quotes').result();
customers = platform.api.get('db-service/_proc/sp_get_customers').result();

var returnData = [
  {
    quotes: quotes,
    customers: customers
  };
]

return returnData;

and your app consumes the custom script.

With SP it’s not possible cause DF just consume the SP and return the result, there isn’t exists a post or pre process to the _proc event.