通过内部属性查询 postgresql jsonb

Question

我正在使用 postgresql 10.6。我的表有一个 jsonb 列 travel，其中填充了以下示例数据。下面是 sqlfiddle；

http://sqlfiddle.com/#!17/e52ff/1

我的 table :

id | travel                                                                                                                                                                                                   
-: | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1 | {"name": "Lucy", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Bangkok", "continent": "Asia"}, {"city": "Paris", "continent": "Europe"}, {"city": "London", "continent": "Europe"}]}       
 2 | {"name": "Tom", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Kyoto", "continent": "Asia"}, {"city": "Frankfurt", "continent": "Europe"}, {"city": "London", "continent": "Europe"}]}      
 3 | {"name": "Lenny", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Bangkok", "continent": "Asia"}, {"city": "New York", "continent": "America"}, {"city": "Seattle", "continent": "America"}]}

DDL 和插入代码:

create table people (
    id serial primary key,
    travel jsonb
);

insert into people (travel) values (
'{
    "name": "Lucy",
    "trips": [
      {
        "continent": "Asia",
        "city": "Tokyo"
      },
      {
        "continent": "Asia",
        "city": "Bangkok"
      },
      {
        "continent": "Europe",
        "city": "Paris"
      },
      {
        "continent": "Europe",
        "city": "London"
      }
    ]
  }
'::jsonb);

insert into people (travel) values (
'{
    "name": "Tom",
    "trips": [
      {
        "continent": "Asia",
        "city": "Tokyo"
      },
      {
        "continent": "Asia",
        "city": "Kyoto"
      },
      {
        "continent": "Europe",
        "city": "Frankfurt"
      },
      {
        "continent": "Europe",
        "city": "London"
      }
    ]
  }
'::jsonb);

insert into people (travel) values (
'{
    "name": "Lenny",
    "trips": [
      {
        "continent": "Asia",
        "city": "Tokyo"
      },
      {
        "continent": "Asia",
        "city": "Bangkok"
      },
      {
        "continent": "America",
        "city": "New York"
      },
      {
        "continent": "America",
        "city": "Seattle"
      }
    ]
  }
'::jsonb);

如何查询亚洲大陆上有"o"字母的城市的行程？

感谢和问候

Answer 1

我觉得你自己的回答就好了。数组选择可以简化一点，大陆过滤条件的重复有点难看 - 我可能会写

SELECT *
FROM (
  SELECT
    travel -> 'name' as name,
    ARRAY(
      SELECT mytrips
      FROM jsonb_array_elements(travel -> 'trips') mytrips
      WHERE mytrips ->> 'continent' = 'Europe'
    ) as trips
  FROM
    people
  ) t
WHERE 
  trips <> '{}'

^{( online demo )}
另一方面，如果您确实有关于 travel 的索引，则 WHERE 子句中的 @> 运算符可能会更快。

分组方法可能更简单，但对于同一个人的多次旅行具有不同的语义:

SELECT travel -> 'name' as name, jsonb_agg(trip) as trips
FROM people, jsonb_array_elements(travel -> 'trips') trip
WHERE trip ->> 'continent' = 'Europe'
GROUP BY name

^{( online demo )}