How to translate the PostgreSQL array_agg function to SQLite?

Question

This query works in PostgreSQL:

  Select    ot.MCode,array_to_string(array_agg(tk1.TName || ',' || ot.TTime), ' - ') as oujyu_name_list
    From    TR_A ot
    inner join MS_B tk1 on ot.Code = tk1.Code
    Where   ot.Code in (Select Code From TR_C ) 
    Group byot.MCode

but it does not work in SQLite, because SQLite does not have the array_agg() function. How can this query be converted to SQLite?

Answer 1

For this query, you can use group_concat, which directly returns a string:

SELECT ..., group_concat(tk1.TName || ',' || ot.TTime, ' - ')
FROM ...

Answer 2

SQLite now has the JSON1 extension (which ships in the default distribution) that can group and create arrays of JSON objects. For example,

select
  ot.MCode,
  json_group_array(json_object('tname', tk1.TName, 'ttime', ot.TTime)) as oujyu_name_list
from TR_A as ot
inner join MS_B as tk1
on (ot.Code = tk1.Code)
where ot.Code in (select code from TR_C)
group by ot.MCode;

The second column will be formatted as a JSON array e.g. [{"tname":...,"ttime":...},...].