sql - 使用SQL Server JSON_VALUE搜索JSON数组

Question

假设一个给定的json对象:

{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}

SQL Server具有JSON_VALUE函数，可在WHERE子句中使用以与特定的json元素进行匹配，例如

WHERE JSON_VALUE(columnName, $.info.address[1].town) = 'Belgrade'

问题在于它需要一个索引。如何搜索路径指定的所有数组元素？

Answer 1

您可以尝试使用OPENJSON和WITH子句(以指定列及其类型)，然后在所有元素中进行搜索:

DECLARE @json nvarchar(max)
SET @json = '{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}'

SELECT Town
FROM OPENJSON(@json, '$.info.address')
WITH (
   Town nvarchar(100) '$.town'
)
WHERE Town = 'Belgrade'

输出:

------------
Town
------------
Belgrade

如果您的JSON数据在表格列中，则也可以使用以下方法:

-- Table
CREATE TABLE #Data (
   Id int,
   JsonData varchar(max)
)
INSERT INTO #Data
   (Id, JsonData)
VALUES 
   (1, N'{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}'),
   (2, N'{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}'),
   (3, N'{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}')


-- Statement
SELECT DISTINCT Id
FROM #Data d
CROSS APPLY (
   SELECT *
   FROM OPENJSON(d.JsonData, '$.info.address')
   WITH (
      Town nvarchar(100) '$.town'
   )
) j   
WHERE (j.Town = 'Belgrade') OR (j.Town = 'Paris')

输出:

-------
Id
-------
1
2
3