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 wikrsh · Accepted Answer · 2016-05-03T20:55:18

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
;

Postgres JSONB do a select from an array of data

1 Answers