Creating a join between a json field and table and getting the results in a query

Posted on

Question :

Example table structure:

Table Issues:

Id
Title
AffectedClients (json field)

Table Clients:

Id
Name

Data is stored like this:

Table: Issues

Id   Title                        AffectedClients
1    Error when clicking save     ["1","2"]
2    404 error on url             ["3"]

Table: Clients

Id   Name
1    Tesco
2    Costa
3    Boots
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?

Answer :

Answer based on comments left by a-horse-with-no-name:

This task would be a lot easier with a properly normalized data model.

For example, with a schema of:

create table issues (id integer primary key, title varchar(100));
create table clients (id integer primary key, name varchar(100));
create table client_issues 
(
  issue_id integer not null references issues, 
  client_id integer not null references clients,
  primary key (issue_id, client_id)
);

insert into issues 
values 
(1,'Error when clicking save'), 
(2, '404 error on url'); 

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

insert into client_issues (issue_id, client_id)
values
(1, 1),
(1, 2),
(2, 3);

The query becomes:

select i.id,  i.title, string_agg(c.name, ',') as affected_clients
from issues i 
  join client_issues cs on cs.issue_id = i.id
  join clients c on c.id = cs.client_id
group by i.id, i.title;

Results:

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

db<>fiddle demo

Leave a Reply

Your email address will not be published.