Postgres: Select all fields where json field array contains a particular value?

Question

Let say I have a users and companies table, where 1 user can work for multiple companies. In this case I model it using JSON field.

users
- id: PK
- jobs: json

Sample jobs field: [{"company_id": 1, "title": "Engineer" }, {"company_id": 2, "title": "Accountant"}]

Given a company ID, is there a way to run 1 SQL query (Postgres 9.4) that can extract all the user IDs that have worked in that company? Something like:

select id from users where map(jobs, "company_id") contains <?>

Sami Kuhmonen · Accepted Answer · 2015-05-14 08:23:49Z

4

Might not be the best way to do it, but this works:

SELECT id FROM
  (SELECT id, json_array_elements(jobs) as a FROM table) b
WHERE a->>'company_id'='1';

answered May 14, 2015 at 8:23

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

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

1 Comment

huy Over a year ago

Thanks! This works. I'm accepting this because this works for json field type too. The other answer by @jgm works too, but it requires your field to be jsonb.

user1596371 · Accepted Answer · 2015-05-14 08:41:11Z

2

This should do the trick:

SELECT id FROM users WHERE jobs @>'[{"company_id":1}]'

answered May 14, 2015 at 8:41

user1596371

3 Comments

Sami Kuhmonen Over a year ago

Just a note: using this requires the jsonb datatype, will not work with json

user1596371 Over a year ago

True but if you're on 9.4 you really should be using jsonb unless you have a specific requirement which precludes it.

Sami Kuhmonen Over a year ago

Yes, that's why just a note. And of course for one-off performance-insensitive queries one can always cast json into jsonb.

Collectives™ on Stack Overflow

Postgres: Select all fields where json field array contains a particular value?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related