Need to parse JSON returned from stored procedure for Logic App

Question

I am trying to return a single order from a SQL Server table as JSON from a stored procedure to create input for an action in a Logic App. The procedure always wraps the result in something more than the simple array of name-value pairs; note: the following examples are from different orders.

I want:

[
  {
    "order_id": "385604",
    "warehouse_id": "1",
    "item_warehouses_overridden": "0",
    "customer_id": "263",
    "customer_po": "Inv- Chad Gracy and Joshua Ruff",
    "date": "02/26/2025",
    "date_internal": "2025-02-26"...

But I get (@jsonResult is the output param of the stored procedure):

{
  "jsonResult": "{\"order_id\":\"385582\",\"warehouse_id\":\"1\",\"item_warehouses_overridden\":\"0\",\"customer_id\":\"1209\",\"customer_po\":\"Stock Knit Headcovers\",\"date\":\"02\\/26\\/2025\",\"date_internal\":\"2025-02-26\"...

My stored procedure is shown here, I've tried using a Parse JSON action in the logic app, but naturally get a schema error.

ALTER PROCEDURE [dbo].[proc_get_order_by_order_id_JSON]
(
    -- Add the parameters for the stored procedure here
    @order_num nvarchar(10),
    @jsonResult NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON

    SELECT @jsonResult = (
        SELECT
            order_id,
            warehouse_id,
            item_warehouses_overridden,
            customer_id,
            customer_po,
            date,
            date_internal
        FROM 
            tbl_orders
        WHERE 
            (order_id = @order_num)
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        );
    
    -- Use JSON_QUERY to avoid escaping quotes
    SELECT @jsonResult = JSON_QUERY(@jsonResult);
END;

How are you getting the results returned like this? In SSMS or in a C# or other application code? If other application code please include that code as well. — Brad, Commented Feb 27 at 15:48
Why do you use WITHOUT_ARRAY_WRAPPER when you do want the wrapper? Why then use SELECT @jsonResult = JSON_QUERY(@jsonResult); afterwards? If you remove both of those, you get the result you want. db<>fiddle — Thom A, Commented Feb 27 at 15:51
Also, your date column isn't a valid date or even a date (it's a (n)varchar); there aren't 26 months in the year. — Thom A, Commented Feb 27 at 15:52
The unwanted results I displayed above are the output of the sql connection action in the logic app. I do not want the wrapper. I think that is the goal. The dates in this table are string values, because this is data imported through an api that returns all string values. — Bob Canavan, Commented Feb 27 at 16:00
You don’t need to return JSON from the stored proc to get that format back to LogicApps, the table of data will come back as JSON anyway. Just run your select and see what happens. — Skin, Commented Feb 27 at 20:12

10p · Accepted Answer · 2025-02-28 09:04:17Z

1

You can convert the output from string to JSON using the json function in your Logic App like this:

json(outputs('SP_Response')?['jsonResult'])

where SP_Response is the name of the action that contains the output of your stored procedure.

Result:

edited Feb 28 at 9:04

answered Feb 27 at 19:16

10p

6,80326 silver badges31 bronze badges

Add a comment |

Collectives™ on Stack Overflow

Need to parse JSON returned from stored procedure for Logic App

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related