Querying Json array column and other current table column

Question

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..

You should be using `OPENJSON` against the **column** *not* a variable, which contains the value from a *single* row. — Thom A, Apr 07 '22 at 17:17
@Larnu then how can I join it with the column NameID and CurrentName? The variable is just for testing purposes. — NoviceDeveloper, Apr 07 '22 at 17:18
Side note, I recommend against `varchar(MAX)` for the data types defined in your `WITH`. Use appropriate data types and lengths. Someone's name is *very* unlikely to be 8,001 characters or more, and certainly won't be up to ~2 billion characters in length. — Thom A, Apr 07 '22 at 17:18
Why do you need to `JOIN`? Aren't all these values in the same table? — Thom A, Apr 07 '22 at 17:19

score 1 · Accepted Answer · answered Apr 07 '22 at 17:21

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.

This works well. I said join because I didn't know what type of join I need. Cross apply worked. — NoviceDeveloper, Apr 07 '22 at 17:47

Querying Json array column and other current table column

1 Answers1