Generating json data and filtering on matching condition from a specific table

Question

Table "ORDER":

ID	ORDERN_NUM
1	123
2	321
3	456

Table "RECEIVERS":

FIRST_NAME	ID	LAST_NAME
Pete	1	Tyler
Sarah	1	Bowden
Bart	2	Simpson

Table "PRODUCTS":

TYPE	ID
Towel	1
Pen	1
Spoon	2

We can say "FIRST_NAME" and "TYPE" are primary keys for their respective tables, but that doesn't matter for this question. The important part is that "ORDER" has primary key "ID", and "ID" is foreign key in "PRODUCTS" and "RECEIVERS".

I've tried to develop a single query, which allows the user to query on "RECEIVERS.FIRST_NAME". The query would then join on all three tables, and return everything related to "ID" = 1. So if I were to query on "Pete", I would be given back an object such as below:

[{
    "ID": "1",
    "ORDERN_NUM": "123",
    "RECEIVERS": [{
        "FIRST_NAME": "Pete",
        "LAST_NAME": "Tyler"
    },
    {
        "FIRST_NAME": "Sarah",
        "LAST_NAME": "Bowden"
    }],
    "PRODUCTS": [{
        "TYPE": "Towel"
    },
    {
        "TYPE": "Pen"
    }]
}]

I so far have this query:

SELECT COUNT(*), 
       JSON_ARRAYAGG(JSON_OBJECT('ID', o.ID, 'RECEIVERS', r.RECEIVERS, 'PRODUCTS', p.PRODUCTS))
FROM       ORDER AS o
INNER JOIN (SELECT ID,
                   JSON_ARRAYAGG(JSON_OBJECT('firstName', FIRST_NAME, 'lastName', LAST_NAME)) RECEIVERS
            FROM RECEIVERS
            GROUP BY ID) r on o.ID = r.ID
INNER JOIN (SELECT ID,
                   JSON_ARRAYAGG(JSON_OBJECT('type', TYPE)) PRODUCTS
            FROM PRODUCTS
            GROUP BY ID) p on o.ID = p.ID
WHERE RECEIVERS.FIRST_NAME = "Pete"

However when searching on FIRST_NAME = 'Pete', it will indeed bring all "ORDER"s back associated with Pete with all "PRODUCTS" in a nested array, and Pete's details in a nested array, however it will not bring back Sarah as a "RECEIVER".

How can I accomplish this?

Answer 1

If I follow you correctly, the problem is with the where clause of the query; it filters on orders that belong to Tyler, while you seem to want orders that have the same id as those that belong to Tyler.

You could simply change this:

where r.FIRST_NAME = "Pete"

To:

where r.id in (select id from receivers where first_name = 'Pete')

In your query:

select count(*), 
    json_arrayagg(json_object('ID', o.ID, 'RECEIVERS', r.RECEIVERS, 'PRODUCTS', p.PRODUCTS))
from orders as o
inner join (
    select id,
        json_arrayagg(json_object('firstName', FIRST_NAME, 'lastName', LAST_NAME)) RECEIVERS
    FROM receivers
    group by id
) r on o.id = r.id
  inner join (
    select id,
        JSON_ARRAYAGG(JSON_OBJECT('type', TYPE)) PRODUCTS
    FROM PRODUCTS
    group by id
) p on o.id = p.id
where r.id in (select id from receivers where first_name = 'Pete')

We could also express this logic with another join:

select count(*), 
    json_arrayagg(json_object('ID', o.ID, 'RECEIVERS', r.RECEIVERS, 'PRODUCTS', p.PRODUCTS))
from orders as o
inner join (
    select id,
        json_arrayagg(json_object('firstName', FIRST_NAME, 'lastName', LAST_NAME)) RECEIVERS
    FROM receivers
    group by id
) r on o.id = r.id
  inner join (
    select id,
        JSON_ARRAYAGG(JSON_OBJECT('type', TYPE)) PRODUCTS
    FROM PRODUCTS
    group by id
) p on o.id = p.id
inner join receivers as r1 on r1.id = o.id
where r1.first_name = 'Pete'