5 Replies Latest reply on Aug 3, 2017 10:39 AM by rareddy

    create a json message

    coderoen

      Hello,

        can somebody help me? I want to create a json message, based on an order and 1 or more order details.

       

      BEGIN
        SELECT JSONOBJECT(northwind.orders.OrderID, northwind.orders.OrderDate, JSONARRAY((SELECT JSONOBJECT(UnitPrice, Quantity) FROM northwind.order_details WHERE northwind.order_details.odr_OrderID = northwind.orders.OrderID)) AS details) AS json_out FROM northwind.orders WHERE northwind.orders.OrderID IN (10248);
      END
      

       

      Error message:

      org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.api.exception.query.ExpressionEvaluationException: 
      TEIID30328 Unable to evaluate JSONOBJECT(Northwind_datasource.orders.OrderID AS OrderID, Northwind_datasource.orders.OrderDate AS OrderDate
      , JSONARRAY((SELECT JSONOBJECT(UnitPrice AS UnitPrice, Quantity AS Quantity) FROM northwind.order_details WHERE northwind.order_details.odr_OrderID = northwind.orders.OrderID LIMIT 2)) AS details): TEIID30345 The command of this scalar subquery returned more than one value: SELECT JSONOBJECT(UnitPrice AS UnitPrice, Quantity AS Quantity) FROM northwind.order_details 
      WHERE northwind.order_details.odr_OrderID = northwind.orders.OrderID LIMIT 2
      

       

       

      One detail, it works.

       

       {"OrderID":10266,"OrderDate":"1996-07-26","details":[{"UnitPrice":30.4,"Quantity":12}]
      

       

      When i create a message by hand, it also works.

       

      SELECT JSONOBJECT(northwind.orders.OrderID, northwind.orders.OrderDate, JSONARRAY(JSONOBJECT(ShipAddress, ShipCity)
      , JSONOBJECT(ShipAddress, ShipCity)) AS addresses) AS json_out FROM northwind.orders WHERE northwind.orders.OrderID IN (10266
      );
      

       

      {"OrderID":10266,"OrderDate":"1996-07-26","addresses":[{"ShipAddress":"Torikatu 38","ShipCity":"Oulu"},{"ShipAddress":"Torikatu 38","ShipCity":"Oulu"}]}
      
        • 1. Re: create a json message
          rareddy
          • 2. Re: create a json message
            coderoen

            yes i did, that's how I learned about the json functions. First I used the xml functions, like the statement below where it is possible to create a message within a select statement.

             

            BEGIN
            SELECT XMLELEMENT(NAME orders, XMLAGG(XMLELEMENT(NAME "order"
            , XMLELEMENT(NAME id, OrderID), XMLELEMENT(NAME "date", OrderDate
            ), XMLELEMENT(NAME details
            , (SELECT XMLAGG(XMLELEMENT(NAME detail, XMLELEMENT(NAME unitprice, UnitPrice
            ), XMLELEMENT(NAME quantity, Quantity), XMLELEMENT(NAME products
            , (SELECT XMLAGG(XMLELEMENT(NAME product, XMLELEMENT(NAME name, ProductName), XMLELEMENT(NAME quantityPerUnit, QuantityPerUnit), XMLELEMENT(NAME inStock, UnitsInStock)))
             FROM northwind.products AS prd WHERE prd_ProductID = ProductID)))) 
            FROM northwind.order_details AS odl WHERE OrderID = odr_OrderID))))) AS xml_out 
            FROM northwind.orders AS odr WHERE OrderID = p_orderID_in;
            END
            

             

            I also like to do that for a JSON message.

            • 3. Re: create a json message
              rareddy

              I believe you need to use jsonarrary_agg instead of jsonarray

              • 4. Re: create a json message
                coderoen

                I also created a statement like this, with the agg function. Had to put the OrderId and the OrderDate in a group by clause.

                 

                SELECT JSONOBJECT(northwind.orders.OrderID, northwind.orders.OrderDate
                , JSONARRAY_AGG((SELECT JSONOBJECT(UnitPrice) 
                FROM northwind.order_details WHERE northwind.order_details.odr_OrderID = northwind.orders.OrderID)) AS details) AS json_out 
                FROM northwind.orders WHERE northwind.orders.OrderID IN (10248) GROUP BY northwind.orders.OrderID, northwind.orders.OrderDate;
                END
                

                 

                Result:

                org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.api.exception.query.ExpressionEvaluationException: TEIID30328 Unable to evaluate (SELECT JSONOBJECT(northwind.order_details.UnitPrice AS UnitPrice) FROM northwind.order_details WHERE odr_OrderID = OrderID LIMIT 2): TEIID30345 The command of this scalar subquery returned more than one value: SELECT JSONOBJECT(northwind.order_details.UnitPrice AS UnitPrice) FROM northwind.order_details WHERE odr_OrderID = OrderID LIMIT 2

                 

                With OrderId = 10266 it works again, because there is one instance of a detail with ID = 10266 (see the table).

                 

                json_out

                -------------------------------------------------------------------------

                {"OrderID":10266,"OrderDate":"1996-07-26","details":[{"UnitPrice":30.4}]}

                 

                OrderID CustomerID EmployeeID OrderDate  UnitPrice Quantity Discount

                ------- ---------- ---------- ---------- --------- -------- --------

                   10248 VINET               5 1996-07-04      14.0       12      0.0

                   10248 VINET               5 1996-07-04       9.8       10      0.0

                   10248 VINET               5 1996-07-04      34.8        5      0.0

                   10266 WARTH               3 1996-07-26      30.4       12     0.05

                   10248 VINET               5 1996-07-04     25.25       10      0.0

                • 5. Re: create a json message
                  rareddy

                  Looks like you need another level of JSONOBJECT to indicate root level object that contains an array of orders with JASONARRAY_AGG