current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 1 down vote favorite

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!

share|improve this question
up vote 2 down vote accepted

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
;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.