PostgreSQL - Where In JSONB

Question

I'm having a hard time querying jsonb data. I'm not used to dealing with jsonb types in postgres . Is it possible to do a WHERE IN on a jsonb field?

I have data like this

id | act   | act_id |      from_ids       |     object_ids       | post_date
2    post      1      {"2":"1494308197"}    {"items":["104564"]}   1494308197

I'm trying to do something like this below to find any ids in the from_ids column where the id is the key.

SELECT an.* FROM activity_network an, jsonb_each(an.from_ids) d WHERE d.key->> in ('2');

But obviously that doesn't work. I tried various forms.

I found a similar question but it doesn't work for what I need as it gets the value and I need to get key using IN.

Not sure how to use these jsonb functions.

Answer 1

It is much simpler than that:

SELECT an.*
FROM activity_network an
WHERE an.from_ids ?| ARRAY['2'];

See the documentation.

The following index can speed up such a query:

CREATE INDEX ON activity_network USING gin (from_ids);

Answer 2

You can use the jsonb_exists function for that.

SELECT an.* FROM activity_network an WHERE jsonb_exists(an.from_ids,'2');

The ? operator described in the PG documentation maps to that function. I suggest you use it instead if you're using PHP, Java or anything other than C + libpq, because everyone in the world has agreed that ? stands for positional parameters, except the Postgres team.