create table store (id integer primary key, name text);
create table opening (store integer references store(id),
wday text, start integer, end integer);
insert into store (name) values ('foo'), ('bar');
insert into opening (store, wday, start, end)
values (1, 'mon', 0, 60),
(1, 'mon', 60, 120),
(1, 'tue', 180, 240),
(1, 'tue', 300, 360),
(2, 'wed', 0, 60),
(2, 'wed', 60, 120),
(2, 'thu', 180, 240);
I'm trying to get in a single query all the stores and their respective openings by weekday as JSON.
{
"1": {
"name": "foo",
"openings": {
"mon": [ [ 0, 60 ], [ 60, 120 ] ],
"tue": [ [180, 240 ], [ 300, 360 ] ]
}
},
"2": {
"name": "bar",
"openings": {
"wed": [ [0,60], [60,120] ],
"thu": [ [180,240] ]
}
}
}
Here's the evolution of what I have tried. I missing a way to do multi-level json_group_object I suppose.
select * from opening;
store wday start end
---------- ---------- ---------- ----------
1 mon 0 60
1 mon 60 120
1 tue 180 240
1 tue 300 360
2 wed 0 60
2 wed 60 120
2 thu 180 240
select * from opening group by store;
store wday start end
---------- ---------- ---------- ----------
1 mon 0 60
2 wed 0 60
select json_group_object(store, wday) from opening group by store;
json_group_object(store, wday)
-----------------------------------------
{"1":"mon","1":"mon","1":"tue","1":"tue"}
{"2":"wed","2":"wed","2":"thu"}
select store, wday, json_group_array(json_array(start, end))
from opening group by store, wday;
store wday json_group_array(json_array(start, end))
---------- ---------- ----------------------------------------
1 mon [[0,60],[60,120]]
1 tue [[180,240],[300,360]]
2 thu [[180,240]]
2 wed [[0,60],[60,120]]
select json_object('id', store,
'openings', json_group_object(wday, json_group_array(json_array(start, end)))
) from opening group by store, wday;
Error: near line 17: misuse of aggregate function json_group_array()
select json_object('id', store,
'openings', json_object(wday, json_group_array(json_array(start, end)))
) from opening group by store, wday;
{"id":1,"openings":{"mon":[[0,60],[60,120]]}}
{"id":1,"openings":{"tue":[[180,240],[300,360]]}}
{"id":2,"openings":{"thu":[[180,240]]}}
{"id":2,"openings":{"wed":[[0,60],[60,120]]}}
How can I group on same id here?
A row will be returned for each unique values corresponding to a group by. Thus, the outermost select must have a group by store.
select json_group_object(store, x)
from (
select
store,
json_object(
'id', store,
'openings', json_object(wday, json_group_array(json_array(start, end)))
) x
from opening group by store, wday
) group by store;
This inner query returns literal JSON however. It seems silly to decode the inner JSON just to then encode it all in the outer-most query.
{"1":"{\"id\":1,\"openings\":{\"mon\":[[0,60],[60,120]]}}","1":"{\"id\":1,\"openings\":{\"tue\":[[180,240],[300,360]]}}"}
{"2":"{\"id\":2,\"openings\":{\"thu\":[[180,240]]}}","2":"{\"id\":2,\"openings\":{\"wed\":[[0,60],[60,120]]}}"}
IIRC in Postgres this inner query that returns JSON wouldn't return literal JSON but either way I'm confused how to continue.
Thanks for any help.
