PostgreSQL jsonb_array_elements with array of Hash elements

Question

I have a PostgreSQL table (creatures) that looks like the following:

| id <integer> | json <jsonb> |
-------------------------------
|      1       | {"name": "Grooper", "weaknesses":[{"type":"Fire", "value":2},{"type":"Dark", "value":2}]} |
|      2       | {"name": "Zik", "weaknesses":[{"type":"Fire", "value":2}]} |

I am trying to create a query that filters on weakness type, such that I can filter on all creatures with weaknesses to types Fire or Dark (contain either one), but also query on creatures with weaknesses of type Fire and Dark (must contain both).

I have figured out a query that works with Fire or Dark:

select 
  distinct c.* 
from 
  creatures c, 
  jsonb_array_elements(json->'weaknesses') as weakness 
where weakness->'type' ?| array['Fire','Dark']

How can I get this query to work to filter on creatures of types Fire and Dark?

UPDATE:

I have a query which seems to accomplish what I want, but it doesn't look pretty. Any tips?

SELECT * FROM creatures
WHERE 'Fire' in (SELECT jsonb_array_elements(json->'weaknesses')->>'type') 
AND 'Dark' in (SELECT jsonb_array_elements(json->'weaknesses')->>'type')

Patrick · Accepted Answer · 2016-04-20 08:49:05Z

1

The problem here is that you have to "look into" the jsonb values inside the array so you can't use the ?& operator. Instead, find the appropriate keys with the ? operator and select only those rows that have two hits:

SELECT DISTINCT id, json
FROM creatures
JOIN LATERAL jsonb_array_elements(json->'weaknesses') ON value ? 'Dark' OR value ? 'Fire'
GROUP BY 1, 2
HAVING count(id) = 2;

By the way, if id is unique (primary key perhaps?) then SELECT DISTINCT ON (id) id, json ... is much faster because the jsonb document does not have to be examined to remove duplicates.

answered Apr 20, 2016 at 8:49

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL jsonb_array_elements with array of Hash elements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related