How do I get nested JSON data out of SQLite with a multi-level group by?

Question

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.

Answer 1

Adding an example for general reference. Shawn's point about using json(x) in the outer selects is key. Here's an example with multiple levels of nested arrays

The sample data: select * from tblSmall

region|subregion    |postalcode|locality                       |lat    |lng    |
------|-------------|----------|-------------------------------|-------|-------|

Delhi |Central Delhi|    110001|Connaught Place                |28.6431|77.2197|
Delhi |Central Delhi|    110001|Parliament House               |28.6407|77.2154|
Delhi |Central Delhi|    110003|Pandara Road                   |28.6431|77.2197|
Delhi |Central Delhi|    110004|Rashtrapati Bhawan             |28.6453|77.2128|
Delhi |Central Delhi|    110005|Karol Bagh                     |28.6514|77.1907|
Delhi |Central Delhi|    110005|Anand Parbat                   |28.6431|77.2197|
Delhi |North Delhi  |    110054|Civil Lines (North Delhi)      |28.6804|77.2263|
Delhi |North Delhi  |    110084|Burari                         |28.7557|77.1994|
Delhi |North Delhi  |    110084|Jagatpur                       |28.7414|77.2199|
Delhi |North Delhi  |    110086|Kirari Suleman Nagar           |28.7441|77.0732|

For each region has multiple subregion values, each subregion has multiple postalcode values, and each postalcode has multiple locality values.

Here's the sql:

select
  json_object(
    'region', A2.region,
    'subregions', json_group_array(json(A2.json_obj2))
  )
from
  (
    select
      A1.region,
      json_object(
        'subregion', A1.subregion,
        'postalCodes', json_group_array(json(A1.json_obj1))
      ) as json_obj2
    from
      (
        select
          region,
          subregion,
          json_object(
            'postalCode', postalcode,
            'localities', json_group_array(
              json_object(
                'locality', locality,
                'latitude', lat,
                'longitude', lng
              )
            )
          ) as json_obj1
        from
          tblSmall
        where
          subregion in ('Central Delhi', 'North Delhi')
        group by
          region,
          subregion,
          postalcode
      ) as A1
    group by
      A1.region,
      A1.subregion
  ) as A2
group by
  A2.region

Note the json(A1.json_obj1) and json(A2.json_obj2) bits to handle the decode/re-encode of json coming out of the inner queries.

Here's the result (kind of long because of pretty-print) - there's a subregions array, which contains a postalcodes array, which contains a localities array:

{
  "region": "Delhi",
  "subregions": [
    {
      "subregion": "Central Delhi",
      "postalCodes": [
        {
          "postalCode": 110001,
          "localities": [
            {
              "locality": "Connaught Place",
              "latitude": 28.6431,
              "longitude": 77.2197
            },
            {
              "locality": "Parliament House",
              "latitude": 28.6407,
              "longitude": 77.2154
            }
          ]
        },
        {
          "postalCode": 110003,
          "localities": [
            {
              "locality": "Pandara Road",
              "latitude": 28.6431,
              "longitude": 77.2197
            }
          ]
        },
        {
          "postalCode": 110004,
          "localities": [
            {
              "locality": "Rashtrapati Bhawan",
              "latitude": 28.6453,
              "longitude": 77.2128
            }
          ]
        },
        {
          "postalCode": 110005,
          "localities": [
            {
              "locality": "Karol Bagh",
              "latitude": 28.6514,
              "longitude": 77.1907
            },
            {
              "locality": "Anand Parbat",
              "latitude": 28.6431,
              "longitude": 77.2197
            }
          ]
        },
        {
          "postalCode": 110060,
          "localities": [
            {
              "locality": "Rajender Nagar",
              "latitude": 28.5329,
              "longitude": 77.2004
            }
          ]
        },
        {
          "postalCode": 110069,
          "localities": [
            {
              "locality": "Union Public Service Commission",
              "latitude": 28.5329,
              "longitude": 77.2004
            }
          ]
        },
        {
          "postalCode": 110100,
          "localities": [
            {
              "locality": "Foreign Post Delhi IBC",
              "latitude": 28.6563,
              "longitude": 77.1366
            }
          ]
        }
      ]
    },
    {
      "subregion": "North Delhi",
      "postalCodes": [
        {
          "postalCode": 110054,
          "localities": [
            {
              "locality": "Timarpur",
              "latitude": 28.7038,
              "longitude": 77.2227
            },
            {
              "locality": "Civil Lines (North Delhi)",
              "latitude": 28.6804,
              "longitude": 77.2263
            }
          ]
        },
        {
          "postalCode": 110084,
          "localities": [
            {
              "locality": "Burari",
              "latitude": 28.7557,
              "longitude": 77.1994
            },
            {
              "locality": "Jagatpur",
              "latitude": 28.7414,
              "longitude": 77.2199
            }
          ]
        },
        {
          "postalCode": 110086,
          "localities": [
            {
              "locality": "Kirari Suleman Nagar",
              "latitude": 28.7441,
              "longitude": 77.0732
            }
          ]
        }
      ]
    }
  ]
}