1

I have a query from a SQL Server database that returns data from linked tables. I would like to display this data in Json format in Python so that i can send it as request to API.

The query comprises data selected from four tables customers, items, invoices, invoice_details.

My query returns this data:

enter image description here

What I need is to convert that data to json format like below:

{
    "invoiceInformation": {
        "invoiceNo": "PI/002020",
        "invoiceDescription": "Local Sales",
    },
    "customerInformation": {
        "customer_name": ""
    },
    "goodsDetails": [
        {
            "item": "bag",
            "itemCode": "GB5003",
            "qty": "2",
            "unitOfMeasure": "each",
            "unitPrice": "150.00",
            "total": "300.00"
            "tax": "45.76",
        },
        {
            "item": "shirt",
            "itemCode": "GB5004",
            "qty": "1",
            "unitOfMeasure": "each",
            "unitPrice": "4000000.00",
            "total": "4000000.00",
            "taxRate": "0.18",
            "tax": "610,169.49",
        }
    ]
}

I would like to extract from the result a single invoice_no grouping objects like below in json.

I would like to do this in python although I am quite new and searching around isn't yielding much.

All examples I land on only show converting a query that returns an array of uniform objects, a single record or nested records like for this case but not this kind where you have to extract groups of a resultset and display them as separate objects at the same level.

I'll appreciate any examples or guidance to any steps.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ken
  • 299
  • 3
  • 15
  • 32

1 Answers1

0

a lot of solutions can make this, do you already have a code in python?

Convert SQL into json in Python