c# - 对 JSON 数据的 SQL LIKE 查询

Question

我将 JSON 数据(无架构)存储在 SQL Server 列中，需要对其运行搜索查询。

例如(非实际数据)

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

SQL Server 2017 具有 JSON_XXXX 方法，但它们适用于已知模式。在我的例子中，对象的模式没有精确定义并且可能会改变。

当前要搜索列，例如找到 Make=Mercedes-Benz。我正在使用搜索短语“%\"Make\":\"Mercedes-Benz\"%"。如果使用确切的品牌名称，这将非常有效。我希望用户也能够使用部分名称进行搜索，例如只需输入“Benz”或“merc”。

是否可以使用适合我的通配符来构建 SQL 查询？还有其他选择吗？

Answer 1

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

表格:

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"  
 }