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?

CL. · Accepted Answer · 2014-11-14 12:03:14Z

18

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

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

answered Nov 14, 2014 at 12:03

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

younes0 Over a year ago

it's worth to mention that unlike the ARRAY_AGG function, you must specify the GROUP_BY clause for GROUP_CONTACT, more infos: stackoverflow.com/questions/22190200/…

CL. Over a year ago

@younes0 All aggregate functions return a single row without GROUP BY; there's no difference between ARRAY_AGG and GROUP_CONCAT.

younes0 Over a year ago

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)

CL. Over a year ago

@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).

Yawar · Accepted Answer · 2022-02-14 16:37:44Z

10

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

answered Feb 14, 2022 at 16:37

Yawar

11.7k4 gold badges53 silver badges88 bronze badges

Collectives™ on Stack Overflow

How to translate the PostgreSQL array_agg function to SQLite?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related