json - 如何使用多级分组从 SQLite 中获取嵌套的 JSON 数据？

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);

我试图在工作日以 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] ]
    }
  }
}

这是我尝试过的演变。我错过了做多级的方法 json_group_object我想。

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]]}}

我如何在这里分组相同的 ID？

将为与 group by 对应的每个唯一值返回一行。 .因此，最外面的 select必须有 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;

然而，这个内部查询返回文字 JSON。将内部 JSON 解码然后将其全部编码到最外层查询中似乎很愚蠢。

{"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]]}}"}

Postgres 中的 IIRC 这个返回 JSON 的内部查询不会返回文字 JSON，但无论哪种方式，我都很困惑如何继续。

谢谢你的帮助。

Answer 1

添加一个示例以供一般引用。 Shawn关于使用 json(x) 的要点在外部选择是关键。这是一个具有多级嵌套数组的示例

样本数据: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|

每个 region有多个 subregion值，每个 subregion有多个 postalcode值，每个 postalcode有多个 locality值。

这是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

请注意 json(A1.json_obj1)和 json(A2.json_obj2)位来处理来自内部查询的 json 的解码/重新编码。

这是结果(由于 pretty-print 而有点长) - 有一个 subregions数组，其中包含 postalcodes数组，其中包含 localities大批:

{
  "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
            }
          ]
        }
      ]
    }
  ]
}