Querying Json array column and other current table column-CodePudding

I have a table that has three columns from which I need to retrieve the data. One of the columns OtherNames contain an array of Json Objects.

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

I can query that column just fine, but how can I join it with the table it is in by ID so I can retrieve all the information on the table and what is related to this row.

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' 
    )

This above results in

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

How can I join to show the NameID and CurrentName along with it ?

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

There could be multiple records and multiple Json data..

CodePudding user response：

As I mentioned in the comments, use OPENJSON against the column, not a scalar variable which contains the value of just one of your rows, and none of the other row data.

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;

Note that as your value [DateTime] is culture dependant, you may need to define it as a varchar(10) in the WITH, and then CONVERT it to a date in the SELECT with a style code.