querying JSONB with array fields

Question

If I have a jsonb column called value with fields such as:

{"id": "5e367554-bf4e-4057-8089-a3a43c9470c0",
 "tags": ["principal", "reversal", "interest"],,, etc}

how would I find all the records containing given tags, e.g: if given: ["reversal", "interest"] it should find all records with either "reversal" or "interest" or both.

My experimentation got me to this abomination so far:

select value from account_balance_updated 
where value @> '{}' :: jsonb and value->>'tags' LIKE '%"principal"%';

of course this is completely wrong and inefficient

Patrick · Accepted Answer · 2016-09-14 03:16:28Z

1

Assuming you are using PG 9.4+, you can use the jsonb_array_elements() function:

SELECT DISTINCT abu.*
FROM account_balance_updated abu,
     jsonb_array_elements(abu.value->'tags') t
WHERE t.value <@ '["reversal", "interest"]'::jsonb;

answered Sep 14, 2016 at 3:16

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

3 Comments

iLemming Over a year ago

eh... now I wonder if it's possible to achieve that by just modifying where part? Otherwise I will have to refactor some other parts of the code (that composes the whole query). So if I have a query that starts like

select value from account_balance_updated  where value @> '{}' :: jsonb and value->> 'id' = '48c017c2-40c0-4462-ba2b-c2f176a2d086' and ...

could I achieve the same trick (as in your answer) by just appending some magic to query like this?

iLemming Over a year ago

I did this and value->'tags' ?| array['interest','reversal'] and it seems to be working

Patrick Over a year ago

Without seeing the whole query it is difficult to say. In SQL there are often multiple solutions for a single problem. The optimal solution can usually be deduced from table structures, indexes, other query content, etc., but sometimes it is just trial-and-error.

iLemming · Accepted Answer · 2016-09-14 20:03:32Z

1

As it turned out you can use cool jsonb operators described here:

https://www.postgresql.org/docs/9.5/static/functions-json.html

so original query doesn't have to change much:

select value from account_balance_updated 
where value @> '{}' :: jsonb and value->'tags' ?| array['reversal', 'interest'];

in my case I also needed to escape the ? (??|) because I am using so called "prepared statement" where you pass query string and parameters to jdbc and question marks are like placeholders for params:

https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

answered Sep 14, 2016 at 20:03

iLemming

36.8k62 gold badges202 silver badges317 bronze badges

Collectives™ on Stack Overflow

querying JSONB with array fields

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related