SQL LIKE query on JSON data

Question

I have JSON data (no schema) stored in a SQL Server column and need to run search queries on it.

E.g. (not actual data)

[
 {
   "Color":"Red",
   "Make":"Mercedes-Benz"  
 },
 {
   "Color":"Green",
   "Make":"Ford"  
 },
]

SQL Server 2017 has JSON_XXXX methods but they work on pre-known schema. In my case, the schema of objects is not defined precisely and could change.

Currently to search the columns e.g. find Make=Mercedes-Benz. I'm using a search phrase "%\"Make\":\"Mercedes-Benz\"%". This works quite well IF exact make name is used. I'd like user to be able to search using partial names as well e.g. just typing 'Benz' or 'merc'.

Is it possible to structure a SQL query using wild cards that'll work for me? Any other options?

Answer 1

If you happen to be running an older version of SQL Server that does not support built-in JSON functions such as OPENJSON(), you can use SQL similar to the following.

You can try testing this SQL at http://sqlfiddle.com/#!18/dd7a5

NOTE: This SQL assumes the key you are searching on only appears ONCE per record/JSON object literal (in other words, you are only storing JSON object literals with unique keys per record/database row). Also note, the SELECT query is UGLY, but it works.

/* see http://sqlfiddle.com/#!18/dd7a5 to test this online*/
/* setup a test data table schema */
CREATE TABLE myData (
   [id] [int] IDENTITY(1,1) NOT NULL,
   [jsonData] nvarchar(4000)
  CONSTRAINT [PK_id] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)
);

/* Insert some test data */
INSERT INTO myData
   (jsonData)
VALUES
   ('{
   "Color":"Red",
   "Make":"Mercedes-Benz"
 }');
 
 INSERT INTO myData
   (jsonData)
VALUES
   (
   '{
   "Color":"White",
   "Make":"Toyota",
   "Model":"Prius",
   "VIN":"123454321"
 }');

INSERT INTO myData
   (jsonData)
VALUES
   (
   '{
   "Color":"White",
   "Make":"Mercedes-Benz",
   "Year": 2009
 }');
 
 INSERT INTO myData
   (jsonData)
VALUES
   (
   '{
   "Type":"Toy",
   "Color":"White",
   "Make":"Toyota",
   "Model":"Prius",
   "VIN":"99993333"
 }');

/* This select statement searches the 'Make' keys, within the jsonData records, with values LIKE '%oyo%'. This statement will return records such as 'Toyota' as the Make value. */
SELECT id, SUBSTRING(
        jsonData
        ,CHARINDEX('"Make":', jsonData) + LEN('"Make":')
        ,CHARINDEX(',', jsonData, CHARINDEX('"Make":', jsonData) + LEN('"Make":')) - CHARINDEX('"Make":', jsonData) - LEN('"Make":')
        ) as CarMake FROM myData
WHERE 
SUBSTRING(
        jsonData
        ,CHARINDEX('"Make":"', jsonData) + LEN('"Make":"')
        ,CHARINDEX('"', jsonData, CHARINDEX('"Make":"', jsonData) + LEN('"Make":"')) - CHARINDEX('"Make":"', jsonData) - LEN('"Make":"')
        ) LIKE '%oyo%'

Answer 2

One possible approach is to use OPENJSON with default schema twice. With default schema, OPENJSON returns table with columns key, value and type, and you can use them for your WHERE clause.

Table:

CREATE TABLE #Data (
   Json nvarchar(max)
)
INSERT INTO #Data
   (Json)
VALUES
   (N'[
 {
   "Color":"Red",
   "Make":"Mercedes-Benz"  
 },
 {
   "Color":"Green",
   "Make":"Ford",
   "Year": 2000
 }
]')

Statement:

SELECT 
   j1.[value]
   -- or other columns 
FROM #Data d
CROSS APPLY OPENJSON(d.Json) j1
CROSS APPLY OPENJSON(j1.[value]) j2
WHERE 
   j2.[key] LIKE '%Make%' AND
   j2.[value] LIKE '%Benz%'

Output:

--------------------------
value
--------------------------
{
   "Color":"Red",
   "Make":"Mercedes-Benz"  
 }

Answer 3

You can split json by ',' and search like this:

WHERE EXISTS (SELECT *  
            FROM STRING_SPLIT(json_data, ',')  
           WHERE value LIKE  '%\"Make\":%'
             AND value LIKE  '%Benz%' 
           );