'How to translate the PostgreSQL array_agg function to SQLite?

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?

postgresqlsqliteandroid-sqliteaggregate-functions


Solution 1:[1]

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":...},...].

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Yawar

Related Questions