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

    create a json message

    jeroen riezebeek Newbie

      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"}]}
      
        • 2. Re: create a json message
          jeroen riezebeek Newbie

          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
            Ramesh Reddy Master

            I believe you need to use jsonarrary_agg instead of jsonarray

            • 4. Re: create a json message
              jeroen riezebeek Newbie

              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
                Ramesh Reddy Master

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