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

notAChance
June 13, 2023
213 views
0 votes
2 Answers

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?

Answers

You can solve your problem with an EXISTS expression right after the join operations, where you check on matching ids and matching first_name.

...
WHERE EXISTS(SELECT 1 
             FROM receivers rec 
             WHERE rec.ID = o.ID 
               AND rec.FIRST_NAME = 'Pete')

Here’s the full query:

WITH receivers_agg AS(
    SELECT ID, 
           JSON_ARRAYAGG(
                JSON_OBJECT('FIRST_NAME', FIRST_NAME, 
                             'LAST_NAME',  LAST_NAME)
           ) AS list_receivers
    FROM receivers
    GROUP BY ID
), products_agg AS(
    SELECT ID, 
           JSON_ARRAYAGG(
                JSON_OBJECT('TYPE', TYPE)
           ) AS list_products
    FROM products
    GROUP BY ID
)
SELECT JSON_OBJECT(        'ID',         o.ID,
                   'ORDERN_NUM', o.ORDERN_NUM,
                    'RECEIVERS', r.list_receivers,
                     'PRODUCTS', p.list_products  ) AS jsondata
FROM       orders        o
INNER JOIN receivers_agg r ON o.ID = r.ID
INNER JOIN products_agg  p ON o.ID = p.ID
WHERE EXISTS(SELECT 1 
             FROM receivers rec 
             WHERE rec.ID = o.ID 
               AND rec.FIRST_NAME = 'Pete')

Check the full demo here.

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'

Please signup or login to give your own answer.

Click here to cancel reply.

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

Answers