How to retreive data from Database

Question

Hi

How to retreive data from Database in a below format

{
  "Version": "1.1",
  "TranDtls": {
    "TaxSch": "GST",
    "SupTyp": "B2B",
    "RegRev": "N",
    "IgstOnIntra": "N"
  },
  "DocDtls": {
    "Typ": "INV",
    "No": "SAMPLES/0002",
    "Dt": "13/01/2021"
  },
  "SellerDtls": {
    "Gstin": "29AAFCC9980M1ZR",
    "LglNm": "GSTZEN DEMO PRIVATE LIMITED",
    "Addr1": "Manyata Tech Park",
    "Loc": "BANGALORE",
    "Pin": 560077,
    "Stcd": "29"
  },
  "BuyerDtls": {
    "Gstin": "06AAMCS8709B1ZA",
    "LglNm": "Quality Products Private Limited",
    "Pos": "06",
    "Addr1": "133, Mahatma Gandhi Road",
    "Loc": "HARYANA",
    "Pin": 121009,
    "Stcd": "06"
  },
  "DispDtls": {
    "Nm": "Maharashtra Storage",
    "Addr1": "133, Mahatma Gandhi Road",
    "Loc": "Bhiwandi",
    "Pin": 400001,
    "Stcd": "27"
  },
  "ShipDtls": {
    "Gstin": "URP",
    "LglNm": "Quality Products Construction Site",
    "Addr1": "Anna Salai",
    "Loc": "Chennai",
    "Pin": 600001,
    "Stcd": "33"
  },
  "ItemList": [
    {
      "ItemNo": 0,
      "SlNo": "1",
      "IsServc": "N",
      "PrdDesc": "Computer Hardware - Keyboard and Mouse",
      "HsnCd": "33059011",
      "Qty": 25,
      "FreeQty": 0,
      "Unit": "PCS",
      "UnitPrice": 200,
      "TotAmt": 5000,
      "Discount": 0,
      "PreTaxVal": 0,
      "AssAmt": 5000,
      "GstRt": 18,
      "IgstAmt": 900,
      "CgstAmt": 0,
      "SgstAmt": 0,
      "CesRt": 0,
      "CesAmt": 0,
      "CesNonAdvlAmt": 0,
      "StateCesRt": 0,
      "StateCesAmt": 0,
      "StateCesNonAdvlAmt": 0,
      "OthChrg": 0,
      "TotItemVal": 5900
    }
  ],
  "ValDtls": {
    "AssVal": 5000,
    "CgstVal": 0,
    "SgstVal": 0,
    "IgstVal": 900,
    "CesVal": 0,
    "StCesVal": 0,
    "Discount": 0,
    "OthChrg": 0,
    "RndOffAmt": 0,
    "TotInvVal": 5900
  },
  "EwbDtls": {
    "TransId": "21ADAPP6261D1Z1",
    "TransName": "Just in time Shippers Pvt Limited",
    "TransMode": "1",
    "Distance": 0,
    "VehNo": "KA33A1234",
    "VehType": "R"
  }
}

Thanks

Eliana Blake · Answer

It seems like you have provided a JSON structure that represents a document with various details such as transaction information, seller and buyer details, item list, and more. To retrieve data from a database in the format you provided, you would typically use a programming language or database querying tool to fetch the required information and then format it into a JSON object similar to the one you shared. Here's a general overview of how you could retrieve this data from a database using, for example, SQL and a programming language like Python: 1. Database Query : You would construct a SQL query to select the relevant data from your database tables. For instance, you might have tables for transactions, sellers, buyers, and items. 2. Database Connection : Establish a connection to your database from your programming language. You can use libraries like `psycopg2` for PostgreSQL, `pymysql` for MySQL, or an ORM like SQLAlchemy. 3. Execute Query : Execute the SQL query from your programming language, retrieve the results into a data structure like a dictionary or list. 4. Format Data : Organize the retrieved data into the structure you provided. Create a dictionary with the appropriate keys and values, following the JSON schema you shared. 5. Convert to JSON : Finally, convert the dictionary to a JSON object using built-in functions like `json.dumps()` in Python. Here's a simplified Python example using SQLite as the database to illustrate the concept: import sqlite3 import json # Establish a connection to the database conn = sqlite3.connect('your_database.db') cursor = conn.cursor() # Execute a sample query (you would customize this query based on your database schema) cursor.execute("SELECT * FROM transactions WHERE transaction_id = '12345'") transaction_data = cursor.fetchone() # Format the retrieved data into a dictionary data_dict = { "Version": "1.1", "TranDtls": { "TaxSch": transaction_data[0], "SupTyp": transaction_data[1], # Add more fields from the retrieved data as needed }, # Include other sections like DocDtls, SellerDtls, BuyerDtls, etc. similarly } # Convert the dictionary to JSON json_data = json.dumps(data_dict, indent=2) print(json_data) # Remember to close the connection conn.close() This is a high-level overview, and the actual implementation would depend on your specific database type, schema, and the programming language you are using. If you have more specific details about your database system or programming language, feel free to provide them for a more tailored explanation.