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?

score 16 · Accepted Answer · answered Nov 14 '14 at 12:03

16

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

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

answered Nov 14 '14 at 12:03

CL.

173,858
17
217
259

1

it's worth to mention that unlike the ARRAY_AGG function, you must specify the GROUP_BY clause for GROUP_CONTACT, more infos: http://stackoverflow.com/questions/22190200/query-using-group-concat-is-returning-only-one-row – younes0 Nov 15 '14 at 13:36
@younes0 *All* aggregate functions return a single row without GROUP BY; there's no difference between ARRAY_AGG and GROUP_CONCAT. – CL. Nov 15 '14 at 13:45
You're right; I was talking about the specific case postgres which allows to omit the group_by (if you query in one table I guess) – younes0 Dec 01 '14 at 10:32
1

@younes0 *All* SQL databases allow to omit the GROUP BY (and then use one group over all rows when you're using any aggregate function). – CL. Dec 01 '14 at 11:24

score 7 · Answer 2 · answered Feb 14 '22 at 16:37

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

How to translate the PostgreSQL array_agg function to SQLite?

2 Answers2

Linked

Related