title: How to I check each item of a json array within the where clause using a like statement
body: <p>I am creating a query which is for a search feild within my application.  The query uses the "like" key word to check a variety of fields within the records.  One of the fields is an unnamed ([{}, {}]) json array. The fields within the array all match.  I want to be able to check each "Value" property of the array without using an index, i.e "$[0].value".  The reason is that the size of the array could vary.  The following is an example of the data:</p>
  
  <pre><code>[{
      "MappedFieldName": "Customer",
      "DataType": "string",
      "Value": "Mapco Express"
  }, {
      "MappedFieldName": "Invoice Nbr",
      "DataType": "string",
      "Value": "31856174"
  }, {
      "MappedFieldName": "Invoice Document Date",
      "DataType": "DateTime",
      "Value": "2018-12-25 00:00:00.000"
  }, {
      "MappedFieldName": "Processing Date",
      "DataType": "DateTime",
      "Value": "2019-01-04 00:00:00.000"
  }, {
      "MappedFieldName": "Vendor Name",
      "DataType": "string",
      "Value": "Bullseye"
  }, {
      "MappedFieldName": "Account Nbr",
      "DataType": "string",
      "Value": "0048219"
  }, {
      "MappedFieldName": "Location #",
      "DataType": "string",
      "Value": "7520"
  }, {
      "MappedFieldName": "Amount Invoiced",
      "DataType": "decimal",
      "Value": "3580.43"
  }, {
      "MappedFieldName": "Amount Processed",
      "DataType": "decimal",
      "Value": "3580.43"
  }, {
      "MappedFieldName": "Invoice Start Date",
      "DataType": "DateTime",
      "Value": "2018-04-01 00:00:00.000"
  }, {
      "MappedFieldName": "Invoice End Date",
      "DataType": "DateTime",
      "Value": "2018-04-01 00:00:00.000"
  }]
  </code></pre>
  
  <p>and</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT *
  FROM [dbo].[Invoice]
  WHERE JSON_VALUE(InvoiceData, '$.Value') like '%' + @searchText + '%'
  </code></pre>
  
  <p>This query does not work as I am not specifying an index, i.e '$[0].Value'.</p>