Get data from Sql as per below schema

i have below Json Schema. i want to get data from Sql as per below schema

{
"$schema": "json-schema.org/draft-04/schema#",
"type": "object",
"properties": {
"supplyType": {
"type": "string",
"maxLength": 1,
"minLength": 1,
"enum": [ "O","I" ],
"description": "Supply Type"
},
"subSupplyType": {
"type": "string",
"description": "Sub Supply Type"
},
"subSupplyDesc": {
"type": "string",
"maxLength": 20,
"description": "Other Sub Supply Description"
},
"docType": {
"type": "string",
"enum": [ "INV", "CHL", "BIL","BOE","OTH" ],
"description": "Document Type"
},
"docNo": {
"type": "string",
"maxLength": 16,
"description": "Document Number (Alphanumeric with / and - are allowed)"
},
"docDate": {
"type": "string",
"pattern": "[0-3][0-9]/[0-1][0-9]/[2][0][1-2][0-9]",
"description": "Document Date"
},
"fromGstin": {
"type": "string",
"maxLength": 15,
"minLength": 15,
"pattern": "[0-9]{2}[0-9|A-Z]{13}",
"description": "From GSTIN (Supplier or Consignor)"
},
"fromTrdName": {
"type": "string",
"maxLength": 100,
"description": "From Trade Name (Consignor Trade name)"
},
"fromAddr1": {
"type": "string",
"maxLength": 120,
"description": "From Address Line 1 (Valid Special Chars #,-,/)"
},
"fromAddr2": {
"type": "string",
"maxLength": 120,
"description": "From Address Line 2(Valid Special Chars # , - ,/)"
},
"fromPlace": {
"type": "string",
"maxLength": 50,
"description": "From Place"
},
"actFromStateCode": {
"type": "integer",
"maximum": 99,
"description": "Actual From State Code"
},
"fromPincode": {
"type": "integer",
"maximum": 999999,
"minimum": 100000,
"description": "From Pincode"
},
"fromStateCode": {
"type": "integer",
"maximum": 99,
"description": "From State Code"
},
"toGstin": {
"type": "string",
"maxLength": 15,
"minLength": 15,
"pattern": "[0-9]{2}[0-9|A-Z]{13}",
"description": "To GSTIN (Consignee or Recipient)"
},
"toTrdName": {
"type": "string",
"maxLength": 100,
"description": "To Trade Name (Consignee Trade name or Recipient Trade name)"
},
"toAddr1": {
"type": "string",
"maxLength": 120,
"description": "To Address Line 1 (Valid Special Chars #,-,/)"
},
"toAddr2": {
"type": "string",
"maxLength": 120,
"description": "To Address Line 2 (Valid Special Chars #,-,/)"
},
"toPlace": {
"type": "string",
"maxLength": 50,
"description": "To Place"
},
"toPincode": {
"type": "integer",
"description": "To Pincode"
},
"actToStateCode": {
"type": "integer",
"maximum": 99,
"description": "Actual To State Code"
},
"toStateCode": {
"type": "integer",
"maximum": 99,
"description": "To State Code"
},
"transactionType": {
"type": "integer",
"maximum": 4,
"description": "Transaction type"
},
"totalValue": {
"type": "number",
"multipleOf": 0.01,
"description": "Sum of Taxable value"
},
"cgstValue": {
"type": "number",
"multipleOf": 0.01,
"description": "CGST value"
},
"sgstValue": {
"type": "number",
"multipleOf": 0.01,
"description": "SGST value"
},
"igstValue": {
"type": "number",
"multipleOf": 0.01,
"description": "IGST value"
},
"cessValue": {
"type": "number",
"multipleOf": 0.01,
"description": "Cess value"
},
"cessNonAdvolValue": {
"type": "number",
"multipleOf": 0.01,
"description": "Cess Non Advol value"
},
"otherValue": {
"type": "number",
"multipleOf": 0.01,
"description": "Other charges, if any "
},
"totInvValue": {
"type": "number",
"multipleOf": 0.01,
"description": "Total Invoice Value (Including taxable value, tax value,and other charges if any)"
},
"transMode": {
"type": "string",
"enum": ["1","2","3","4"],
"description": "Mode of transport (Road-1, Rail-2, Air-3, Ship-4) "
},
"transDistance": {
"type": "string",
"description": "Distance (<4000 km) "
},
"transporterName": {
"type": "string",
"maxLength": 100,
"description": "Name of the transporter"
},
"transporterId": {
"type": "string",
"pattern": "[0-9]{2}[0-9|A-Z]{13}",
"description": "15 Digit Transporter GSTIN/TRANSIN"
},
"transDocNo": {
"type": "string",
"maxLength": 15,
"description": "Transport Document Number (Alphanumeric with / and – are allowed)"
},
"transDocDate": {
"type": "string",
"description": "Transport Document Date"
},
"vehicleNo": {
"type": "string",
"minLength": 7,
"maxLength": 15,     
"description": "Vehicle Number"
},
"vehicleType": {
"type": "string",
"description": "Vehicle Type"
},
"itemList": {
"type": "array",
"items": [
{
"type": "object",
"properties": {
"productName": {
"type": "string",
"maxLength": 100,
"description": "Product / Item Name"
},
"productDesc": {
"type": "string",
"maxLength": 100,
"description": "Product / Item description"
},
"hsnCode": {
"type": "number",
"description": "HSN Code"
},
"quantity": {
"type": "number",
"description":"Quantity"
},
"qtyUnit": {
"type": "string",
"maxLength": 3,
"minLength": 3,
"description": "Unit"
},
"taxableAmount": {
"type": "number",
"multipleOf": 0.01,
"description":"Taxable Amount"
},
"sgstRate": {
"type": "number",
"multipleOf": 0.001,
"description":"SGST Rate of Tax"
},
"cgstRate": {
"type": "number",
"multipleOf": 0.001,
"description":"CGST Rate of Tax"
},
"igstRate": {
"type": "number",
"multipleOf": 0.001,
"description":"IGST Rate of Tax"
},
"cessRate": {
"type": "number",
"multipleOf": 0.001,
"description":"Cess Rate of Tax"
},
"cessNonadvol": {
"type": "number",
"description":"Cess Non-Advolerum"
}
},
"required": [
"hsnCode",
"taxableAmount"
]
}
]
}
},
"required": [
"supplyType",
"subSupplyType",
"docType",
"docNo",
"docDate",
"fromGstin",
"fromPincode",
"fromStateCode",
"toGstin",
"toPincode",
"toStateCode",
"transDistance",
"itemList",
"actToStateCode",
"actFromStateCode",
"totInvValue",
"transactionType"
] }

Thanks

It seems like you have a well-defined JSON schema outlining the structure of the data you want to retrieve from a SQL database. The schema provides detailed specifications for each field, including data types, constraints, and descriptions.

To extract data from a SQL database that aligns with this schema, you need to design SQL queries that retrieve information according to the specified structure. Here are some key points to consider based on the JSON schema provided:

1. Table Design: Create SQL tables that correspond to the different sections of your JSON schema. Each table should have columns matching the properties defined in the schema.

2. Querying Data: Use SQL SELECT statements to fetch data from the database. You may need to JOIN multiple tables to gather all the required information, especially when dealing with nested structures like the "itemList" array.

3. Data Validation: Ensure that the retrieved data complies with the constraints specified in the schema. For instance, validate the length of strings, format of dates, and numerical values.

4. Transformations: If needed, transform the SQL query results into a JSON format that adheres to the specified schema. This transformation can be done programmatically in your application code.

5. Error Handling: Implement error handling mechanisms to deal with scenarios where the retrieved data does not meet the schema requirements. You may need to clean or modify the data before transforming it into JSON.

By following these steps and considering the details in your JSON schema, you can effectively retrieve data from a SQL database that conforms to the specified structure. If you need further assistance or have specific requirements, feel free to provide additional details for a more tailored approach.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Emily Foster1y ago
It seems like you have a well-defined JSON schema outlining the structure of the data you want to retrieve from a SQL database. The schema provides detailed specifications for each field, including data types, constraints, and descriptions.

To extract data from a SQL database that aligns with this schema, you need to design SQL queries that retrieve information according to the specified structure. Here are some key points to consider based on the JSON schema provided:

1. Table Design: Create SQL tables that correspond to the different sections of your JSON schema. Each table should have columns matching the properties defined in the schema.

2. Querying Data: Use SQL SELECT statements to fetch data from the database. You may need to JOIN multiple tables to gather all the required information, especially when dealing with nested structures like the "itemList" array.

3. Data Validation: Ensure that the retrieved data complies with the constraints specified in the schema. For instance, validate the length of strings, format of dates, and numerical values.

4. Transformations: If needed, transform the SQL query results into a JSON format that adheres to the specified schema. This transformation can be done programmatically in your application code.

5. Error Handling: Implement error handling mechanisms to deal with scenarios where the retrieved data does not meet the schema requirements. You may need to clean or modify the data before transforming it into JSON.

By following these steps and considering the details in your JSON schema, you can effectively retrieve data from a SQL database that conforms to the specified structure. If you need further assistance or have specific requirements, feel free to provide additional details for a more tailored approach.
0