postgresql - JSONB字段过滤器特定对象中的Postgresql查询对象数组

Question

CREATE TABLE company (id SERIAL, companyJson JSONB);
CREATE INDEX comapny_gin_idx ON company USING gin (companyJson);

INSERT INTO company (id, companyJson) 
  VALUES (1, '[{"name": "t", "company": "company1"}]');

INSERT INTO company (id, companyJson) 
  VALUES (2, '[{"name": "b", "company":"company2"}, {"name": "b", "company":"company3"}]');


 SELECT * FROM company WHERE companyJson @> '[{"company": "company2" , "name": "b"}]';

上述程序的输出是

2   [{"name": "b", "company": "company2"}, {"name": "b", "company": "company3"}]

无论如何要返回 {"name": "b", "company": "company2"} 而不是整行。

标签： postgresqljsonbpostgresql-11

Answer 1

您可以直接返回companyJson -> 0包含->操作数的第一个组件，通过参数零返回第一个组件：

 SELECT companyJson -> 0 as companyJson
   FROM company 
  WHERE companyJson @> '[{"company": "company2" , "name": "b"}]';

Demo