I have the two tables assets
and transactions
in my database, whereby transactions are connected to assets by the columns transactions.from_id
and transactions.to_id
:
assets:
id | title |
---|---|
1 | a1 |
2 | a2 |
3 | a3 |
transactions:
id | title | from_id | to_id |
---|---|---|---|
1 | t1 | 1 | 2 |
2 | t2 | 2 | 3 |
I would like to query the assets table and add a column transactions
to the result, where all transactions related to an asset are listed in a json array like so:
desired query result:
id | title | transactions |
---|---|---|
1 | a1 | [{"id": 1, "title": "t1"}] |
2 | a2 | [{"id": 1, "title": "t1"}, {"id": 2, "title": "t2"}] |
3 | a3 | [{"id": 2, "title": "t2"}] |
I have difficulties to get all connected transactions into one array since the transactions can either be connected to an asset by the column from_id or to_id. My current status is this:
query:
WITH mod_assets AS (
SELECT
a.id,
a.title,
json_array(
json_object(
'id', t_ex.id,
'title', t_ex.title
)
) expenses,
json_array(
json_object(
'id', t_in.id,
'title', t_in.title
)
) incomes
FROM assets AS a
LEFT JOIN transactions AS t_ex ON a.id = t_ex.from_id
LEFT JOIN transactions AS t_in ON a.id = t_in.to_id
)
SELECT * FROM mod_assets;
result:
id | title | expenses | incomes |
---|---|---|---|
1 | a1 | [{"id": 1, "title": "t1"}] | [{"id": null, "title": null}] |
2 | a2 | [{"id": 2, "title": "t2"}] | [{"id": 1, "title": "t1"}] |
3 | a3 | [{"id": null, "title": null}] | [{"id": 2, "title": "t2"}] |
But this is not what I want. I also tried a bunch of other queries and maybe the closest I got was this one, but the GROUP BY
statement does not combine the json array on transactions but returns only the first transaction:
query:
WITH mod_assets AS (
SELECT
a.id,
a.title,
t.id AS t_id,
t.title AS t_title
FROM assets AS a
INNER JOIN transactions AS t
ON a.id = t.from_id
UNION
SELECT a.id, a.title, t.id, t.title
FROM assets AS a
INNER JOIN transactions AS t
ON a.id = t.to_id
)
SELECT
mod_assets.id,
mod_assets.title,
json_array(
json_object(
'id', mod_assets.t_id,
'title', mod_assets.t_title
)
) transactions
FROM mod_assets
GROUP BY mod_assets.id
result:
id | title | transactions |
---|---|---|
1 | a1 | [{"id": 1, "title": "t1"}] |
2 | a2 | [{"id": 1, "title": "t1"}] |
3 | a3 | [{"id": 2, "title": "t2"}] |
Can somebody explain what I need to do?
2
Answers
Assuming you were using SQL Server:
It seems you don’t actually want recursive results (which would have implied you want transactions connected to other transactions, recursively).
You can union together two joins onto
transactions
, in anAPPLY
, then useFOR JSON PATH
to aggregate them into a single JSON.MySQL syntax is a bit different, but essentially the same concept: do a lateral join, with the aggregation inside.
Here’s something that might work for mysql:
You can replace the unions with your tables. GROUP_CONCAT collects the values together and CONCAT creates the necessary array.
Btw, I had to dust off 20 years old mysql knowledge, next time try to set correct database provider in your tags
Edit different version without string functions: