时间戳

首页 > 解决方案 > 具有多个部分匹配条件的sql查询

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

问题描述

我有一个表格列，如下所示。什么是我可以使用多个部分匹配条件的 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"}

标签： sqlsql-serverpostgresql

解决方案

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()是这个的关键。第一个参数可以是 a VARCHAR2，我也尝试使用BLOB包含文本，它可以工作。第二个参数是需要测试的json对象属性的路径，带有条件。

我为和写了两个ORed 条件，但也许有更好的 JSON 查询表达式可以用来包含它们。IDName

有关json_exists()功能的更多信息在这里。

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

在这里查看我为您创建的示例。

这是一个在线测试您的 JSON 查询的在线工具。

在 SO 中也检查这个问题。

微软 SQL 服务器 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数组，并从中创建类似表格的东西，也可以通过访问其列来查询。

我创建的示例在这里。

LIKE文字查询

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

如果您希望 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

推荐阅读