时间戳

首页 > 解决方案 > 列表中所有数字都存在的 Json 数组的 SQL 查询

json - 列表中所有数字都存在的 Json 数组的 SQL 查询

问题描述

我myNumbers在 SQL 中有一个列名，它是一个 json 字符串。json 是一个整数列表。我想查询该表，并且只取回我提供的列表中存在 json 中所有整数的记录。

| myNumbers |
-------------
| [1, 2, 3] |
| [1, 2]    |
| [4, 5, 6] |
| [7, 8, 9] |

示例：我想要所有匹配 numbers 的记录(1, 2)。我只会取回第二项（[1, 2]）。如果我想要所有匹配数字的记录(1, 2, 3)，我会同时取回[1, 2, 3]和[1, 2]

我想做的是：

select * from myTable where JSON_VALUE(myNumbers, '$') in (1, 2, 3)

但这并不好。但是，如果我尝试相同的方法但获取索引，它会起作用：

select * from myTable where JSON_VALUE(myNumbers, '$[0]') in (1, 2, 3)

我尝试了其他一些方法，openjson但json_query没有运气。

有没有办法做到这一点？

标签： jsonsql-server

解决方案

这是关系除法的一个示例，请参见Joe Celko等人。

您可以按如下方式解决此问题：
让我们假设所需的整数在单列 TVP 中传递（我会索引它）

SELECT *
FROM myTable
WHERE NOT EXISTS(
    SELECT [value]
    FROM OPEN_JSON(myNumbers, '$') WITH ([value] int '$')
    EXCEPT
    SELECT * FROM @tvp)

如果你想对数字进行硬编码，有一个更简单的方法：

SELECT *
FROM myTable
WHERE NOT EXISTS(
    SELECT [value]
    FROM OPEN_JSON(myNumbers, '$') WITH ([value] int '$')
    WHERE [value] NOT IN (1,2,3))

我们要求所有行： 没有不在列表中的 JSON 整数，即所有行。如果列表的大小很大，可能有更快的方法来做到这一点，但这是最直接的。

推荐阅读