CODEWITHSUNDEEP
jsonsql-serverjoin
ang
ang

Reputation: 1581

How to merge json arrray with table (SQL Server)

I have two rows of json that I would like to join on id into a single select.

Sample Table A

a
[{id: 1, name: "Alice"},{id:2, name: "Bob"}]
[{id: 5, name: "Charlie"},{id:6, name: "Dale"}

Sample Table B

id age
1 30
2 32
3 20
4 14

Desired Output

c
[{id: 1, name: "Alice", age: 30},{id:2, name: "Bob", age: 32}]
[{id: 5, name: "Charlie", age: 20},{id:6, name: "Dale", age: 14}]

I'd like to do something like

select 
id,
name, 
age
from openJson(tableA) ta
with (
  id int '$.id',
  name nvarchar(50) '$.name'
)
inner join (
  select *
  from tableB tb
) on tb.id = ta.id
for json path

Upvotes: 0

Views: 155

Answers (1)

Thom A
Thom A

Reputation: 95564

Firstly, this assumes that your JSON is actually valid. None of your elements (id and name) are quoted, so the JSON is actually invalid. Also I assume your expected results are wrong, as Charlie is give the age of 20, but that age belongs to someone with an id with the value 3, and Charlie's has an id of 5.

Anyway, we can can achieve this with a subquery:

CREATE TABLE dbo.TableA (a nvarchar(MAX));
INSERT INTO dbo.TableA (a)
VALUES(N'[{"id": 1, "name": "Alice"},{"id":2, "name": "Bob"}]'),
      (N'[{"id": 5, "name": "Charlie"},{"id":6, "name": "Dale"}]');
GO
CREATE TABLE dbo.TableB (id int, age int);
INSERT INTO dbo.TableB (id, age)
VALUES (1,30),
       (2,32),
       (3,20),
       (4,14);
GO

SELECT (SELECT OJ.id,
               OJ.[name],
               B.age
        FROM OPENJSON(A.a)
             WITH (id int,
                   [name] nvarchar(50)) OJ
             LEFT JOIN dbo.TableB B ON OJ.id = B.id
        FOR JSON AUTO) a
FROM dbo.TableA A;
     
     
GO
DROP TABLE dbo.TableA;
DROP TABLE dbo.TableB;

db<>fiddle

Upvotes: 1

Related Questions