查询Json数组列和其他当前表列

Question

我有一个表，它有三列，我需要从中检索数据。其中一列OtherNames包含Json对象的数组。

CREATE TABLE Persons (
NameID int,
CurrentName varchar(255),
OtherNames varchar(max),
);

我可以很好地查询该列，但如何将它与ID所在的表连接起来，以便检索表上的所有信息以及与此行相关的信息。

DECLARE @test VARCHAR(MAX)
SELECT @test = '[{"Name":"Bob","DateTime":"03/03/2022"},{"Name":"Adam","DateTime":"04/05/2022"}]'
SELECT * FROM OPENJSON(@test)
WITH (
Name VARCHAR(MAX) '$.Name',
DateTime VARCHAR(MAX) '$.DateTime' 
)

以上结果导致

Bob     03/03/2022 
Adam    04/05/2022 

如何加入以显示NameID和CurrentName？

NameID   Name      DateTime    CurrentName
1        Bob         03/03/2022  Rob
1        Adam        04/05/2022  Rob

可能存在多个记录和多个Json数据。。

Answer 1

正如我在注释中提到的，对列使用OPENJSON，而不是标量变量，它只包含一行的值，而不包含其他行的数据。

SELECT P.NameID,
ONs.[Name],
ONs.[DateTime],
P.CurrentName
FROM dbo.Persons P
CROSS APPLY OPENJSON(P.OtherNames)
WITH ([Name] varchar(255),
[DateTime] date) ONs;

请注意，由于您的值[DateTime]依赖于区域性，您可能需要在WITH中将其定义为varchar(10)，然后使用样式代码将其CONVERT定义为SELECT中的date。