'PostgreSQL: select data by array of keys jsonb

Let's talk about functions in PostgresSQL. I would like to select all rows that contain the corresponding key in jsonb.

This would be best explained with an example that works:

SELECT x, y, z#>'{"aaa","bbb","ccc"}' as value
FROM "foo"
WHERE "x" = '1' AND "y" = '2'

This will return the correct result for me. Now I would like to return all rows, but if I had 2 jsonb key.

SELECT x, y, z->('[{"aaa","bbb","ccc"},{"ddd","eee","fff"}]') as value
FROM "foo"
WHERE "x" = '1' AND "y" = '2'

This query returns me one result, but the value column is null.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source