如何搜索包含JSON数组的SQL列 [英] How to search SQL column containing JSON array
本文介绍了如何搜索包含JSON数组的SQL列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个具有单个JSON数组的SQL列:
I have a SQL column that has a single JSON array:
{"names":["Joe","Fred","Sue"]}
给出搜索字符串,如何使用SQL在names数组中搜索匹配项?我正在使用SQL 2016,并查看了JSON_QUERY,但不知道如何在JSON数组上搜索匹配项.像下面这样的东西会很好.
Given a search string, how can I use SQL to search for a match in the names array? I am using SQL 2016 and have looked at JSON_QUERY, but don't know how to search for a match on a JSON array. Something like below would be nice.
SELECT *
FROM table
WHERE JSON_QUERY(column, '$.names') = 'Joe'
推荐答案
要在JSON数组中进行搜索,需要使用OPENJSON
For doing a search in a JSON array, one needs to use 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
一起使用.
or as an alternative, one can use it with CROSS APPLY
.
SELECT * FROM
@table
CROSS APPLY OPENJSON(Col,'$.names')
WHERE value ='Joe'
这篇关于如何搜索包含JSON数组的SQL列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文