测试 Postgres json 数组中的项目成员身份

Question

假设我在Postgres中有一个表，看起来像这样 - 请注意zips字段是json。

cities
name (text)    | zips (json)
San Francisco  | [94100, 94101, ...]
Washington DC  | [20000, 20001, ...]

现在我想做一些类似 select * from cities where zip=94101 ，换句话说，测试成员资格。

我尝试使用WHERE zips ? '94101'并得到了operator does not exist: json ? unknown.

我尝试使用WHERE zips->'94101'但不确定该放什么，因为Postgres抱怨argument of WHERE must by type boolean, not type json。

我在这里想要什么？我将如何为 9.3 和 9.4 解决这个问题？

编辑 是的，我知道我应该使用本机数组类型...我们使用的数据库适配器不支持此功能。

Answer 1

在 PostgreSQL 9.4+ 中，您可以使用@>运算符jsonb类型：

create table test (city text, zips jsonb);
insert into test values ('A', '[1, 2, 3]'), ('B', '[4, 5, 6]');
select * from test where zips @> '[1]';

这种方法的另一个优点是 9.4 的新 GIN 索引，可以加快对大表的此类查询。

Answer 2

对于 PostgreSQL 9.4+，你应该使用 json[b]_array_elements_text() ：
（包含运算符?执行类似操作，但对于 JSON 数组，它只能找到完全匹配，这只有在数组包含字符串而不是数字时才会发生）

create table cities (
  city text,
  zips jsonb
);
insert into cities (city, zips) values
    ('Test1', '[123, 234]'),
    ('Test2', '[234, 345]'),
    ('Test3', '[345, 456]'),
    ('Test4', '[456, 123]'),
    ('Test5', '["123", "note the quotes!"]'),
    ('Test6', '"123"'), -- this is a string in json(b)
    ('Test7', '{"123": "this is an object, not an array!"}');
-- select * from cities where zips ? '123';
-- would yield 'Test5', 'Test6' & 'Test7', but none of you want
-- this is a safe solution:
select cities.*
from   cities
join   jsonb_array_elements_text(
         case jsonb_typeof(zips)
           when 'array' then zips
           else '[]'
         end
       ) zip on zip = '123';
-- but you can use this simplified query, if you are sure,
-- your "zips" column only contains JSON arrays:
select cities.*
from   cities
join   jsonb_array_elements_text(zips) zip on zip = '123';

对于 9.3，您可以使用 json_array_elements()（并手动将 zip 转换为 text）：

select cities.*
from   cities
join   json_array_elements(zips) zip on zip::text = '123';

注意：对于 9.3，您无法使查询安全（至少很容易），您只需在zips列中存储 JSON 数组。此外，上面的查询不会找到任何字符串匹配项，您的数组元素必须是数字。

注意2：对于9.4+，您也可以将安全解决方案与json一起使用（不仅使用jsonb，但您必须调用json_typeof(zips)而不是jsonb_typeof(zips)）。

编辑：实际上，@>运算符在PostgreSQL 9.4+中更好，正如@Ainar-G所提到的（因为它是可索引的）。一点旁注：如果你的列和查询都使用 JSON 数字（或 JSON 字符串，但不混合），它只会找到行。

Answer 3

对于 9.3，您可以使用 json_array_elements() .我现在无法在 9.4 版中使用 jsonb 进行测试。

create table test (
  city varchar(35) primary key,
  zips json not null
);
insert into test values
('San Francisco', '[94101, 94102]');
select * 
from (
  select *, json_array_elements(zips)::text as zip from test
) x 
where zip = '94101';