具有多个部分匹配条件的sql查询

Question

我有一个表列，如下所示。我可以使用什么sql查询语句来拥有多个部分匹配条件？

按ID或名称搜索

如果搜索abc，则列出行A1、行A2

如果搜索测试，则列出行A1、行A2、行3

如果搜索ghj，则列出行A2

我试着这样做，但没有任何结果：

 SELECT * FROM table where colB LIKE '"ID":"%abc%"'

​

​

更新文本中的数据

{"ItemId":"123","IDs":[{"ID":"abc","CodingSystem":"cs1"}],"Name":"test itemgh"}

{"ItemId":"123","IDs":[{"ID":"ghj","CodingSystem":"cs1"}],"Name":"test abc"}

{"ItemId":"123","IDs":[{"ID":"defg","CodingSystem":"cs1"}],"Name":"test 111"}

Answer 1

JSON解析

甲骨文

研究了Oracle的JSON解析功能，我设法运行了如下查询：

select * from table t where json_exists(t.colB, '$.IDs[?(@.ID=="abc")]') or json_exists(t.colB, '$.IDs?(@.name=="abc"')

在相同的JSON查询表达式中：

select * from table t where json_exists(t.colB, '$.IDs[?(@.ID=="abc" || @.name=="abc")]')

函数json_exists()的调用是实现此目标的关键。第一个参数可以是VARCHAR2，我也尝试使用包含文本的BLOB，它可以工作。第二个参数是需要测试的json对象属性的路径，条件为。

我为ID和Name编写了两个JSON条件，但是也许有一个更好的OR查询表达式可以将它们都包含进来。

有关json_exists()函数here的更多信息。

Postgres

Postgres中有一种JSON数据类型，支持查询中的解析。因此，如果您的colB列被声明为JSON，您可以这样做：

select * from table where colB->>'Name' LIKE '%abc%';

为了使IDs数组的数组元素可用，您应该使用函数json_array_elements()。

select * from table, json_array_elements(colB->'IDs') e where colB->>'Name' LIKE '%abc%' or e->>'ID' = 'abc';

查看我为您创建的一个示例here。

Here是一个用于在线测试JSON查询的在线工具。

还可以在SO中检查this问题。

MSSQL Server 2017

我还用MS SQL Server做了几个测试，并设法创建了一个在名称字段中搜索部分匹配的示例。

select * from table where JSON_VALUE(colB,'$.Name') LIKE '%abc%';

最后，我得到了一个工作查询，它与Name字段进行部分匹配，与ID字段完全匹配，如下所示：

select * from table t
    CROSS APPLY OPENJSON(colB, '$.IDs') WITH (
        ID VARCHAR(10),
        CodingSystem VARCHAR(10)
    ) e
where JSON_VALUE(t.colB,'$.Name') LIKE '%abc%'
or e.ID = 'abc';

问题是我们需要打开IDs数组，并从它创建一个类似于表的东西，也可以通过访问它的列来查询。

我创建的示例是here。

喜欢文本查询

您的尝试很好，但您弄错了%符号。它们必须是给定字符串中的第一个和最后一个：

如果希望ID为给定值：

SELECT * FROM table where colB LIKE '%"ID":"abc"%'

如果给定值可以在任何位置，则不要将"ID"部分放入：

SELECT * FROM table where colB LIKE '%abc%'

如果给定值只能在ID或Name字段上，则：

SELECT * FROM table where colB LIKE '%"ID":"abc"%' OR colB LIKE '%"Name":"abc"%'

因为您给出的字段的硬编码标识符(例如ID和名称)可以是可变大小写的：

SELECT * FROM table where lower(colB) LIKE '%"id":"abc"%' OR lower(colB) LIKE '%"name":"abc"%'

假设空格的数量在:字符和属性的值或名称之间没有变化。

对于部分匹配，您可以在两者之间使用更多的%，如'%"name":"%abc%"%'

SELECT * FROM table where lower(colB) LIKE '%"id":"abc"%' OR lower(colB) LIKE '%"name":"%abc%"%'

正则表达式

另一种选择是使用正则表达式进行测试。考虑检查以下内容：Oracle extract json fields using regular expression with oracle regexp_substr