SQL view / query to join data between 2 tables via a json field

Question

Example table structure:

create table issues
(id int, title varchar(50), affectedclients varchar(max))

create table clients
(id int, name varchar(50))

insert into issues (id, title, affectedclients) values (1, 'Error when clicking save', '["1","2"]');
insert into issues (id, title, affectedclients) values (2, '404 error on url', '[3]');

insert into clients (id, name) values (1, 'Tesco');
insert into clients (id, name) values (2, 'Costa');
insert into clients (id, name) values (3, 'Boots');
insert into clients (id, name) values (4, 'Nandos');

I want to run a query so that I can get the data in the following format:

Id   Title                        AffectedClients
1    Error when clicking save     Tesco, Costa
2    404 error on url             Boots

How can I achieve this please in the most performant way possible?

If this will be very easy with a properly normalized database then please provide an example.

I am stuck at creating a sql query that extracts the data in the format I mentioned above. — jasear, Sep 26 '19 at 23:07

Zhorov · Accepted Answer · 2019-09-27T14:22:23.120

You need to use OPENJSON() with explicit schema definition to parse the JSON text in the affectedclients column. After that you need to aggregate the names (using FOR XML PATH for SQL Server 2016+ or STRING_AGG() for SQL SQL Server 2017+).

Data:

create table issues
(id int, title varchar(50), affectedclients varchar(max))
create table clients
(id int, name varchar(50))
insert into issues (id, title, affectedclients) values (1, 'Error when clicking save', '["1","2"]');
insert into issues (id, title, affectedclients) values (2, '404 error on url', '[3]');
insert into clients (id, name) values (1, 'Tesco');
insert into clients (id, name) values (2, 'Costa');
insert into clients (id, name) values (3, 'Boots');
insert into clients (id, name) values (4, 'Nandos');

Statement for SQL Server 2016+:

SELECT 
   i.id, 
   i.title,
   [affectedclients] = STUFF(
      (
      SELECT CONCAT(', ', c.[name])
      FROM OPENJSON(i.affectedclients) WITH (id int '$') j
      LEFT JOIN clients c on c.id = j.id
      FOR XML PATH('')
      ), 1, 2, '')
FROM issues i

Statement for SQL Server 2017+:

SELECT i.id, i.title, STRING_AGG(c.name, ', ') AS affectedclients
FROM issues i
CROSS APPLY OPENJSON(i.affectedclients) WITH (id int '$') j
LEFT JOIN clients c ON c.id = j.id
GROUP BY i.id, i.title
ORDER BY i.id, i.title

Results:

-----------------------------------------------
id  title                       affectedclients
-----------------------------------------------
1   Error when clicking save    Tesco, Costa
2   404 error on url            Boots

SQL view / query to join data between 2 tables via a json field

1 Answers1