如何搜索包含JSON数组的SQL列

Question

我有一个 SQL 列，其中有一个单一的 JSON 数组：

{"names":["Joe","Fred","Sue"]}

给定一个搜索字符串，我如何使用SQL在名字数组中搜索匹配项？我正在使用SQL 2016，并已查看JSON_QUERY，但不知道如何在JSON数组上搜索匹配项。像以下这样的东西会很好。

SELECT *
FROM table
WHERE JSON_QUERY(column, '$.names') = 'Joe'

- paultechguy

Answer 1

要在JSON数组中进行搜索，需要使用OPENJSON

DECLARE @table TABLE (Col NVARCHAR(MAX))
INSERT INTO @table VALUES ('{"names":["Joe","Fred","Sue"]}')

SELECT * FROM @table 
WHERE 'Joe' IN ( SELECT value FROM OPENJSON(Col,'$.names'))  

或者作为另一种选择，可以使用CROSS APPLY。

SELECT * FROM 
    @table 
    CROSS APPLY OPENJSON(Col,'$.names')
WHERE value ='Joe'

- Serkan Arslan

---

SQL Server 参考：https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15 - Yogi

Answer 2

Postgres语法

当您知道包含数据的关键字时：

SELECT column_name from table_name where column_name->>'key' LIKE '%QUERYSTRING%';

当您不知道包含数据的关键字时：

SELECT column_name from table_name where column_name::text LIKE '%QUERYSTRING%';

- Kauê Oliveira

Answer 3

您可以通过以下查询在JSON数组中搜索匹配项：

SELECT JSON_EXTRACT(COLUMN, "$.names") AS NAME
FROM TABLE JSON_EXTRACT(COLUMN, "$.names") != ""

将TABLE替换为您的表名，将COLUMN替换为表中的列名。 我提到的关键字名称是根据您的问题而命名的。

- Sudeep Jain

Answer 4

仅想补充现有答案的一个简单解决方案，即如何检查 JSON 中的数组是否包含某个值：

DECLARE @Json NVARCHAR(max) = '{"names":["Joe","Fred","Sue"]}'
IF EXISTS (SELECT value FROM OPENJSON(@Json,'$.names') WHERE value = 'Joe')
    PRINT 'Array contains "Joe"'
ELSE
    PRINT 'Array does not contain "Joe"'

- Mykhailo Seniutovych

Answer 5

这非常简单，可以使用JSON_CONTAINS()函数轻松完成。

SELECT * FROM  table
where  JSON_CONTAINS(column, 'joe','$.name');

- vishwampandya

---

MySQL 参考文档：https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html - Yogi

这仅适用于MySQL，而问题是关于SQL 2016的。 - Select0r

终于，有个简单又有效的东西！ - undefined