CODEWITHSUNDEEP
postgresqljsonbpostgresql-11
Balu
Balu

Reputation: 33

Postgresql query array of objects in JSONB field filter Specific Object

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.

Upvotes: 0

Views: 55

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

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

Upvotes: 1

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions