在9.3及以上版本的Postgresql中查询嵌套载荷的json。

Question

使用PostgreSQL 9.3(及其新的JSON功能)，如果我有一个名为'races'的简单表格，其中包含两列描述，如下所示：

race-id integer,
race-data json

JSON是每个比赛的有效载荷，类似于：

{      "race-time": some-date,
  "runners": [ { "name": "fred","age": 30, "position": 1 },
               { "name": "john","age": 29, "position": 3 },
               { "name": "sam","age": 31, "position": 2 } ],
  "prize-money": 200    }

我该如何查询表格以获得以下结果：

1）Sam获得第一名的比赛

2）Sam获得第一名，John获得第二名的比赛

3）大于30岁的跑步者数量> 5且奖金> 5000的比赛

迄今为止，我的实验（特别是在查询嵌套数组有效负载时）已经进一步规范化数据，即创建一个名为“runners”的表，以便进行此类查询。理想情况下，我想使用这种新型json查询技术，但就这3个简单查询而言，我似乎无法弄清它的相关性。

- Mâtt Frëëman

Answer 1

您可以将 JSON 展开为一条记录，然后按照您希望的方式进行查询（请参见json 函数）：

with cte as (
    select
       race_id,
       json_array_elements(r.race_data->'runners') as d,
       (r.race_data->>'prize-money')::int as price_money
    from races as r
), cte2 as (
    select
        race_id, price_money,
        max(case when (d->>'position')::int = 1 then d->>'name' end) as name1,
        max(case when (d->>'position')::int = 2 then d->>'name' end) as name2,
        max(case when (d->>'position')::int = 3 then d->>'name' end) as name3
    from cte
    group by race_id, price_money
)
select *
from cte2
where name1 = 'sam' and name2 = 'john'

sql fiddle演示

由于你的JSON结构有点复杂，所以情况有些棘手。我认为，如果你稍微改变一下结构，你的查询可能会更简单：

{
  "race-time": some-date,
  "runners":
   {
      "1": {"name": "fred","age": 30},
      "2": {"name": "sam","age": 31},
      "3": {"name": "john","age": 29}
   },
  "prize-money": 200
}

您可以使用->>和->运算符或json_extract_path_text函数获取所需的数据，然后在where子句中使用它：

select *
from races as r
where
    r.race_data->'runners'->'1'->>'name' = 'sam';

select *
from races as r
where
    json_extract_path_text(r.race_data, 'runners','1','name') = 'sam' and
    json_extract_path_text(r.race_data, 'runners','2','name') = 'john';

select *
from races as r
where
    (r.race_data->>'prize-money')::int > 100 and
    (
        select count(*)
        from json_each(r.race_data->'runners')
        where (value->>'age')::int >= 30
    ) >= 2

sql fiddle演示

- Roman Pekar