I have a JSON document stored in a SQL Server table column. The JSON text contains a few arrays, one for handling charges. I need to pull a distinct scalar value from an array based on a WHERE clause condition. For example, I need to retrieve the value "amountCharge" from the "handlingCharge" array where an invoice amount (@Parameter value) falls between the "amountInvoiceFrom" and "amountInvoiceTo" values of the array. If the JSON array were rows in the database, it would be rather simple to write:
SELECT DISTINCT AmountCharge FROM HandlingCharge where @InvoiceTotal BETWEEN AmountInvoiceFrom AND AmountInvoiceTo;
All the JSON examples I have found use JSON_VALUE, but then spell out a specific array indexer (e.g. '$.handlingCharge[1], which presupposes the exact array instance is known. It's not and must be found based on the @InvoiceTotal parameter.
{ "handlingCharge": [ { "amountCharge": 10, "amountInvoiceFrom": 0, "amountInvoiceTo": 100, "rowState": 1, "schema": "ModelHandlingCharge", "updatedBy": "Bill" }, { "amountCharge": 5, "amountInvoiceFrom": 100.01, "amountInvoiceTo": 200, "rowState": 1, "schema": "ModelHandlingCharge", "updatedBy": "Bill" }, { "amountCharge": 0, "amountInvoiceFrom": 200.01, "amountInvoiceTo": 999999999, "rowState": 1, "schema": "ModelHandlingCharge", "updatedBy": "Bill" } ], "isDefault": false, "isEnabled": false, "name": "Acme Supplies", "shippingCharge": [ { "amountInvoiceFrom": 0, "amountInvoiceTo": 100, "percentDiscount": 0, "rowState": 1, "schema": "ModelShippingCharge", "updatedBy": "Bill" }, { "amountInvoiceFrom": 100.01, "amountInvoiceTo": 400, "percentDiscount": 50, "rowState": 1, "schema": "ModelShippingCharge", "updatedBy": "Bill" }, { "amountInvoiceFrom": 400.01, "amountInvoiceTo": 999999999, "percentDiscount": 100, "rowState": 1, "schema": "ModelShippingCharge", "updatedBy": "Bill" } ], "address": [ { "attentionOf": "Wyle E. Coyote", "codeCountry": "US", "codePostal": "82001", "codePostalPlus": "1234", "codeState": "WY", "effective": "2019-08-04T21:12:10.621573Z", "isDpv": true, "nameCity": "My city", "nameCountry": "United States Of America", "nameState": "Wyoming", "streetOrPOBox": "101 Any Street", "type": 1, "rowState": 1, "schema": "ModelAddress", "updatedBy": "Bill" } ], "emailContact": [], "telephone": [ { "number": "123-456-7890", "type": 1, "rowState": 1, "schema": "ModelTelephone", "updatedBy": "Bill" }, { "number": "123-444-2222", "type": 2, "rowState": 1, "schema": "ModelTelephone", "updatedBy": "Bill" } ], "websiteUrl": "http://www.someUrl.com", "rowState": 3, "schema": "ModelCompany", "updatedBy": "Bill" }