JSON数据的SQL LIKE查询 [英] SQL LIKE query on JSON data

问题描述

我在SQL Server列中存储了JSON数据(无模式)，并且需要在其上运行搜索查询.

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

例如(不是实际数据)

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

SQL Server 2017具有JSON_XXXX方法，但它们可用于已知的架构.在我的情况下，对象的架构没有精确定义，并且可能会更改.

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.

当前正在搜索列，例如找到Make = Mercedes-Benz.我正在使用搜索短语％\" Make \:\" Mercedes-Benz \％".如果使用确切的品牌名称，则效果很好.我希望用户能够使用部分名称进行搜索，例如只需输入"Benz"或"merc".

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

是否可以使用对我有用的通配符构造SQL查询?还有其他选择吗?

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

推荐答案

一种可能的方法是将OPENJSON与默认架构一起使用两次.使用默认架构，OPENJSON 返回表，其中包含列key，value和type，您可以将它们用于WHERE子句.

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.

表格:

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

声明:

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

输出:

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

这篇关于JSON数据的SQL LIKE查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！