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'.

score 0 · Answer 1 · answered Nov 13 '19 at 01:38

I figured it out. I first use OPENJSON on the json field of the record which a search text filter in order to get the index of the portion of the json array which the text is found in. Next I use the index within the where clause to identify the array index to look up. The code to handle this is below. This will return all records where the search text is found within the records json array.

declare @searchText varchar(200) = '004'
declare @searchIndex varchar(10)

SELECT @searchIndex = [key]
FROM OPENJSON((SELECT InvoiceData FROM [dbo].[Invoice])) where Json_Value(value, '$.Value') like '%' + @searchText + '%'

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

This answer could likely be simplified. Feel free to post a simplified answer if you have one.

Zhorov · Answer 2 · 2019-11-13T13:03:34.790

The input JSON is an array of JSON objects with fixed structure (MappedFieldName, DataType and Value keys), so another possible approach is to use OPENJSON() with explicit schema to return a table with columns that you defined in the WITH clause. With this approach you can filter the invoices table and/or get additional information from the input JSON:

Table:

CREATE TABLE Invoices (
   InvoiceData nvarchar(max)
)
INSERT INTO Invoices 
   (InvoiceData)
VALUES
   (N'[{ "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"}]')

Statement:

DECLARE @search nvarchar(max) = '004'
SELECT 
   i.*,
   -- You may include the keys and values from the input JSON:
   j.*
FROM Invoices i
CROSS APPLY OPENJSON(i.InvoiceData) WITH (
   -- You may define only the columns, that you need here:
   [MappedFieldName] nvarchar(100) '$.MappedFieldName',
   [DataType] nvarchar(20) '$.DataType',
   [Value] nvarchar(100) '$.Value' 
) j
WHERE j.[Value] LIKE CONCAT('%', @search, '%')

Salman A · Accepted Answer · 2019-11-13T07:21:42.810

0

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

edited Nov 13 '19 at 07:21

answered Nov 13 '19 at 07:10

Salman A

262,204
82
430
521

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

3 Answers3