英文:
Query json array with no property names
问题
我有一个包含标签字符串数组的nvarchar(max)列。我正在使用JSON函数查询这些列以匹配值。虽然我已经让它工作了，但我不确定是否有其他内置函数可以更好地实现这一点。
我看到了很多查询JSON数据的方法，不确定这是否是查询这些数据的最佳实践。JSON_QUERY使用了$.property的格式，但我没有这个，因为这是一个简单的单维数组。
是否有更好的方法？
英文:
I have a nvarchar(max) column that contains a string array of tags. I am querying this columns for matching values using the JSON functions. While I do have it working, I wasn't sure if there was a better way using other built-in functions.
IF(NOT EXISTS(SELECT 1 FROM #temp))
BEGIN
CREATE TABLE #temp (Id int IDENTITY, Tags nvarchar(max));
INSERT INTO #temp (Tags) VALUES ('["red", "blue", "green"]');
INSERT INTO #temp (Tags) VALUES ('["red", "yellow", "blue"]');
INSERT INTO #temp (Tags) VALUES ('["red", "purple", "orange"]');
END
SELECT * FROM #temp CROSS APPLY OPENJSON(Tags) WITH (T nvarchar(100) '$') tags WHERE tags.T = 'red';
SELECT * FROM #temp CROSS APPLY OPENJSON(Tags) WITH (T nvarchar(100) '$') tags WHERE tags.T = 'blue';
I've seen quite a few ways to query JSON data and wasn't sure if this was the best practice for querying this data. JSON_QUERY uses a $.property format that I don't have because this is a simple single-dimension array.
Is there a better way?
答案1
得分: 1
我们可以利用OPENJSON返回3列：key，value和type。
示例查询：SELECT * FROM OPENJSON('["v1", "v2", "v3"]')
输出：
key value type
--- --- ---
0 v1 1
1 v2 1
2 v3 1
我们可以使用这个来构建查询：
-- 如果存在表table1则删除
CREATE TABLE table1 (
ID INT NOT NULL PRIMARY KEY,
Tags NVARCHAR(100) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO table1
VALUES
(1, '["v1", "v2", "v3"]'),
(2, '["q1", "q2", "q3"]'),
(3, '["z1", "z2", "z3"]')
GO
-- 查找包含JSON数组中的单个值的行：
SELECT * FROM table1
WHERE 'v2' IN (
SELECT value -- 这里我们使用由OPENJSON()返回的`value`
FROM OPENJSON(table1.Tags)
)
-- 查找包含多个值中至少一个值的行（第一个或第二个或...）：
SELECT * FROM table1
WHERE EXISTS (
SELECT 1
FROM OPENJSON(table1.Tags)
WHERE value IN ('v1', 'v2', 'z3') -- 这里我们使用由OPENJSON()返回的`value`
)
第一个查询的输出是数据行1，因为其JSON包含等于'v2'的value：
ID Tags
--- ---
1 ["v1", "v2", "v3"]
第二个查询的输出是数据行1和3，因为它们匹配'v1'、'v2'或'z3'：
ID Tags
--- ---
1 ["v1", "v2", "v3"]
3 ["z1", "z2", "z3"]
英文:
We can use the fact that OPENJSON returns 3 columns: key, value and type.
Example query: SELECT * FROM OPENJSON('["v1", "v2", "v3"]')
Output:
key value type
--- --- ---
0 v1 1
1 v2 1
2 v3 1
We can use this to construct the query:
-- DROP TABLE IF EXISTS table1
CREATE TABLE table1 (
ID INT NOT NULL PRIMARY KEY,
Tags NVARCHAR(100) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO table1
VALUES
(1, '["v1", "v2", "v3"]'),
(2, '["q1", "q2", "q3"]'),
(3, '["z1", "z2", "z3"]')
GO
-- To find rows where a single value is present in the JSON array:
SELECT * FROM table1
WHERE 'v2' IN (
SELECT value -- Here we use `value` that is returned by OPENJSON()
FROM OPENJSON(table1.Tags)
)
-- To find rows containing at least 1 of multiple values (first OR second OR ...):
SELECT * FROM table1
WHERE EXISTS (
SELECT 1
FROM OPENJSON(table1.Tags)
WHERE value IN ('v1', 'v2', 'z3') -- Here we use `value` returned by OPENJSON()
)
Output of first query is data row 1 because its JSON contains a value that is equal to 'v2':
ID Tags
--- ---
1 ["v1", "v2", "v3"]
Output of second query is data rows 1 and 3 because they match 'v1' or 'v2' or 'z3':
ID Tags
--- ---
1 ["v1", "v2", "v3"]
3 ["z1", "z2", "z3"]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论