Postgres JSONB do a select from an array of data

Question

I'm using a Postgres database and I'm trying to use the new JSONB type. I have a table named employees with a column named previous_companies that contains the following JSON data:

[{"company":"Facebook", "link": "www.facebook.com"}, {"company":"Google", "link": "www.google.com"}, {"company":"Some Face", "link": "www.someface.com"}]

I'm trying to select all the employees that have certain string for the field "company", for example:

If I want all the employees that worked on a company, that has "face" on it's name I would have: [{"company":"Facebook", "link": "www.facebook.com"}, {"company":"Some Face", "link": "www.someface.com"}]

I was able to do a query for the EXACT string, like this:

SELECT * FROM employees WHERE previous_companies @> '[{"company":"Facebook"}]'

but it returns this: [{"company":"Facebook", "link": "www.facebook.com"}]

As you can see this does not support querying for incomplete strings.

Thanks!

wikrsh · Accepted Answer · 2016-05-03 20:55:18Z

4

jsonb_array_elements() function may be helpful for querying by array JSONB column:

SELECT
  id,
  to_jsonb(array_agg(previous_company)) AS previous_companies
FROM (
  SELECT
    id,
    jsonb_array_elements(previous_companies) AS previous_company
  FROM ( VALUES
       ('id1', '[{"company":"Facebook", "link": "www.facebook.com"},{"company":"Google", "link": "www.google.com"}, {"company":"Some Face", "link": "www.someface.com"}]'::jsonb),
       ('id2', '[{"company":"Some Face", "link": "www.someface.com"}]'::jsonb),
       ('id3', '[{"company":"Google", "link": "www.google.com"}]'::jsonb)
  ) employees (id, previous_companies)
) T
WHERE
  lower(previous_company->>'company') LIKE '%face%'
GROUP BY
  id
;

answered May 3, 2016 at 20:55

wikrsh

3411 silver badge5 bronze badges

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

Collectives™ on Stack Overflow

Postgres JSONB do a select from an array of data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related