0

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.

Improve this question

2 Answers 2

Reset to default
4

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.

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

5 Comments

So I actually don't see that function in the documentation. Where did you find it?
You can find it by running \doS+ in the psql console. It only lists the corresponding functions when you have the +. It's a few pages down.
But how to represent it WHERE products_alias.extras -> 'SIZE' ?| array['91', '38'] with jsonb_exists ? @Laurenz Albe
@shuba.ivan the -> operator corresponds to either jsonb_array_element(array,index) or jsonb_object_field(object,field) in your case.
but how to should look query WHERE products_alias.extras -> 'SIZE' ?| array['91', '38'] ?
3

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);
Improve this answer

2 Comments

Well look at that. That is simple. Been banging my head for hours. Thanks for the quick one! It doesn't seem to use the gin index if there is more than one item in the array. Would you happen to know if that can be indexed?
I have extended the answer to include that information. If you don't see the index used, maybe PostgreSQL thinks that a sequential scan is cheaper. You can SET enable_seqscan = off to test if the index can be used.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.