Postgresql query array of objects in JSONB field filter Specific Object

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"}]';

The output of the above program is

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

Is there anyway to return {"name": "b", "company": "company2"} instead whole row.

version is PostgreSQL 11.5

Balu
– Balu

2020-02-13 14:54:09 +00:00
Commented Feb 13, 2020 at 14:54 — Balu
– Balu, Commented Feb 13, 2020 at 14:54

user330315 · Accepted Answer · 2020-02-13 15:07:26Z

1

I can only think of unnesting the array and the return the element from that:

SELECT x.j
FROM company c
  cross join jsonb_array_elements(c.companyjson) as x(j)
where x.j = '{"company": "company2" , "name": "b"}'

answered Feb 13, 2020 at 15:07

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Balu Over a year ago

Does this uses indexing which i have created on the table. Since one record may contain 5000 array of json objects.

user330315 Over a year ago

@Balu: no I don't think so, but you can see for yourself by using explain (analyze) select ..

Barbaros Özhan · Accepted Answer · 2020-02-13 15:20:52Z

1

You can directly return the first component through companyJson -> 0 which contains -> operand returning the first component by argument zero :

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

Demo

answered Feb 13, 2020 at 15:20

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

Postgresql query array of objects in JSONB field filter Specific Object

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related