question

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"
}