How to I check each item of a json array within the where clause using a like statement

Question

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:

[{
    "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"
}]

and

SELECT *
FROM [dbo].[Invoice]
WHERE JSON_VALUE(InvoiceData, '$.Value') like '%' + @searchText + '%'

This query does not work as I am not specifying an index, i.e '$[0].Value'.

Answer 1

The query could simply be written as follows. It'll return 1 invoice when there is one or more match in the corresponding JSON column:

SELECT *
FROM invoice
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(invoicedata)
    WITH (
        [Value] NVARCHAR(100) '$.Value'
    )
    WHERE [Value] LIKE '%' + '004' + '%'
)

Demo on db<>fiddle