Query to return all transactions of an asset as a JSON array

Question

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?

Answer 1

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 an APPLY, then use FOR JSON PATH to aggregate them into a single JSON.

SELECT
  a.id,
  a.title,
  t.transactions
FROM assets AS a
CROSS APPLY (
    SELECT
      t.id,
      t.title
    FROM transactions AS t
    WHERE a.id = t.from_id

    UNION ALL

    SELECT
      t.id,
      t.title
    FROM transactions AS t
    WHERE a.id = t.to_id

    FOR JSON PATH
) AS t(transactions);

---

MySQL syntax is a bit different, but essentially the same concept: do a lateral join, with the aggregation inside.

SELECT
  a.id,
  a.title,
  t.transactions
FROM assets AS a
CROSS JOIN LATERAL (
    SELECT
      json_arrayagg(
        json_object(
          'id', mod_assets.t_id,
          'title', mod_assets.t_title
        )
      ) AS transactions
    FROM (
        SELECT
          t.id,
          t.title
        FROM transactions AS t
        WHERE a.id = t.from_id

        UNION ALL

        SELECT
          t.id,
          t.title
        FROM transactions AS t
        WHERE a.id = t.to_id
    ) AS t
) AS t;

Answer 2

Here's something that might work for mysql:

select *
, (select CONCAT('[', GROUP_CONCAT(json_object('id', id, 'title',title)), ']') from (
    select  1, 't1', 1, 2
    union all  select 2, 't2', 2, 3
) t (id,title,from_id,to_id)
  where assets.id IN (from_id, to_id)
  
) as transactions
from (
    select  1, 'a1'
    union all   select 2, 'a2'
    union all   select 3, 'a3'
) assets (id,title)

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:

select *
, (select JSON_ARRAYAGG(JSON_OBJECT('id', id, 'title',title)) from (
    select  1, 't1', 1, 2
    union all  select 2, 't2', 2, 3
) t (id,title,from_id,to_id)
  where assets.id IN (from_id, to_id) 
) as transactions
from (
    select  1, 'a1'
    union all   select 2, 'a2'
    union all   select 3, 'a3'
) assets (id,title)