时间戳

首页 > 解决方案 > SQL JSON 乘以交叉应用

json - SQL JSON 乘以交叉应用

问题描述

我有以下代码，用于在 JSON 列的内容中搜索 id 值。该代码运行良好，在本例中返回 6 行。

SELECT log
FROM logs
CROSS APPLY OPENJSON([log], '$.tags') WITH (id INT '$.id')
WHERE id = 12

我尝试使用以下代码将搜索扩展到另一列

SELECT *
FROM logs
CROSS APPLY OPENJSON([log_x], '$.tags') WITH (id_x INT '$.id')
CROSS APPLY OPENJSON([log_y], '$.tags') WITH (id_y INT '$.id')
WHERE id_x = 1

这会在创建重复行时返回 16 个值。有谁知道如何在不创建重复项的情况下组合多个“交叉应用”？

下面是我用来创建表的一些示例代码

INSERT INTO A10000000075.dbo.Logs ([log_x], [log_y])
VALUES ('{"tags":[{"id_x":1,"x":99.12343123213,"y":88.123232},{"id_x":12,"x":99.12343123213,"y":88.123232},{"id_x":13,"x":99.12343123213,"y":88.123232}]}',
'{"tags":[{"id_y":1006,"x":99.12343123213,"y":88.123232},{"id_y":12,"x":99.12343123213,"y":88.123232},{"id_y":13,"x":99.12343123213,"y":88.123232}]}');

标签： jsonsql-server

解决方案

请试试这个：

SELECT a.[id_x],a.[id_y]
FROM (
    SELECT TRY_CONVERT(INT,JSON_VALUE(x.value,'$.id_x')) AS [id_x]
        ,TRY_CONVERT(INT,JSON_VALUE(y.value,'$.id_y')) AS [id_y]
    FROM logs
    CROSS APPLY OPENJSON([log_x], '$.tags') AS x
    CROSS APPLY OPENJSON([log_y], '$.tags') AS y
    WHERE x.[key] = y.[key]
) a
WHERE a.id_x = 1
;

推荐阅读