Question 110952 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – Best way to join line items on sales order

I have what is probably a remedial question, but I am struggling to come up with the best way to implement it through searching. I am working on a retool workflow that will get the latest shipped sales orders, and send an email. The database I am querying has a Sales Order -> Line Items relationship.

Simplified Example:
SalesOrderTable

SalesOrderID Customer SoldDate ShipDate
1 Customer1 12/7/22 12/7/22
2 Customer1 12/7/22 12/7/22
3 Customer2 12/7/22 12/7/22

Lines on the sales orders

LineItemID LineNumber SalesOrderID Product Price
1 1 1 Eggs $1.00
2 2 1 Milk $1.00
3 1 2 Bread $1.00
4 2 2 Eggs $1.00
5 3 2 Milk $1.00
6 1 3 Eggs $1.00

I am unsure the best way to join the lines to the sales orders. Obviously simply joining the data isn’t really what I want, I don’t think. Meaning

SELECT * FROM SalesOrder
JOIN lines ON SalesOrder.SalesOrderID = Lines.SalesOrderID
WHERE SalesOrderID = 2

Would produce something like(don’t think this is the right way to accomplish):

SalesOrderID Customer SoldDate ShipDate LineItemID LineNumber SalesOrderID Product Price
2 Customer2 12/7/22 12/7/22 3 1 2 Bread $1.00
2 Customer2 12/7/22 12/7/22 4 2 2 Eggs $1.00
2 Customer2 12/7/22 12/7/22 5 3 2 Milk $1.00

Ultimately I am trying to feed this data into Carbone.io from Retool Workflow to make a PDF sales order using workflow, which I believe needs to be JSON similar to below

"SalesOrderID": 2,
"Customer": "Customer2",
"SoldDate": "12/7/22",
"LineItems": {ArrayOfLineItems}

I’ve tried selecting all Sales orders and then using a workflow loop to iterate over each one, selecting all the line items for each line in the sales order. This gives me the line items for each sales order, but I can’t seem to figure out how to get the line items and sales order in the same JSON.

Would I be better to try to write a query that includes all the lines as a JSON array in a column?

Sorry for the long post, but appreciate any feedback or suggestions on the best way to attempt this!

Tags:

2

Answers


  1. 0

    Something like this?

    SELECT * FROM SalesOrder
JOIN Lines1 ON SalesOrder.SalesOrderID = Lines1.SalesOrderID and Lines1.LineItemId= 1
LEFT OUTER JOIN Lines2 ON SalesOrder.SalesOrderID = Lines2.SalesOrderID and Lines2.LineItemId= 2
LEFT OUTER JOIN Lines3 ON SalesOrder.SalesOrderID = Lines3.SalesOrderID and Lines3.LineItemId= 3

    Here is the stuff function. Might get you closer to the format you’re looking for.

    select t.*
,(SELECT
        DISTINCT
            STUFF((select ',' + l.Product
                    FROM LineTable l
                    WHERE l.SalesOrderId = t.SalesOrderId
                    FOR XML path('')),1,1,'') as 'Product'
    ) 
from SalesTable t
    Login or Signup to reply.
  2. 0

    You can get the JSON directly in Oracle using:

    SELECT JSON_OBJECT(
         KEY 'salesOrderId' VALUE s.salesOrderId,
         KEY 'customer'     VALUE s.customer,
         KEY 'soldDate'     VALUE s.soldDate,
         KEY 'shipDate'     VALUE s.shipDate,
         KEY 'lineItems'    VALUE l.lineItems FORMAT JSON
         RETURNING CLOB
       ) AS salesorder
FROM   salesorder s
       CROSS JOIN LATERAL (
         SELECT JSON_ARRAYAGG(
                  JSON_OBJECT(
                    KEY 'lineItemId' VALUE l.lineItemid,
                    KEY 'lineNumber' VALUE l.lineNumber,
                    KEY 'product'    VALUE l.product,
                    KEY 'price'      VALUE l.price
                  )
                  ORDER BY l.lineNumber
                  RETURNING CLOB
                ) AS lineItems
         FROM   lines l
         WHERE  l.salesorderid = s.salesorderid
       ) l

    Which, for the sample data:

    CREATE TABLE SalesOrder (SalesOrderID, Customer, SoldDate, ShipDate) AS
SELECT 1,   'Customer1', DATE '2022-07-12', DATE '2022-07-12' FROM DUAL UNION ALL
SELECT 2,   'Customer1', DATE '2022-07-12', DATE '2022-07-12' FROM DUAL UNION ALL
SELECT 3,   'Customer2', DATE '2022-07-12', DATE '2022-07-12' FROM DUAL;

CREATE TABLE Lines (LineItemID, LineNumber, SalesOrderID, Product, Price) AS
SELECT 1,   1,  1,  'Eggs',  1.00 FROM DUAL UNION ALL
SELECT 2,   2,  1,  'Milk',  1.00 FROM DUAL UNION ALL
SELECT 3,   1,  2,  'Bread', 1.00 FROM DUAL UNION ALL
SELECT 4,   2,  2,  'Eggs',  1.00 FROM DUAL UNION ALL
SELECT 5,   3,  2,  'Milk',  1.00 FROM DUAL UNION ALL
SELECT 6,   1,  3,  'Eggs',  1.00 FROM DUAL;

    Outputs:

    SALESORDER
    {"salesOrderId":1,"customer":"Customer1","soldDate":"2022-07-12T00:00:00","shipDate":"2022-07-12T00:00:00","lineItems":[{"lineItemId":1,"lineNumber":1,"product":"Eggs","price":1},{"lineItemId":2,"lineNumber":2,"product":"Milk","price":1}]}
    {"salesOrderId":2,"customer":"Customer1","soldDate":"2022-07-12T00:00:00","shipDate":"2022-07-12T00:00:00","lineItems":[{"lineItemId":3,"lineNumber":1,"product":"Bread","price":1},{"lineItemId":4,"lineNumber":2,"product":"Eggs","price":1},{"lineItemId":5,"lineNumber":3,"product":"Milk","price":1}]}
    {"salesOrderId":3,"customer":"Customer2","soldDate":"2022-07-12T00:00:00","shipDate":"2022-07-12T00:00:00","lineItems":[{"lineItemId":6,"lineNumber":1,"product":"Eggs","price":1}]}

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search