Existance of key in JSON array in PostgreSQL

Question

I've got a problem that I can't quite figure out.

Let's say I have a column called movies that look like this (jsonb):

[{"title": "Pulp Fiction"}, {"tiitle": "Rambo"}]

is there a way to find rows where movies has an object with the key tiitle?

Answer 1

For Postgres 9.4:

ds=# CREATE TABLE yourtable (movie jsonb);
CREATE TABLE
ds=# \d+ yourtable 
                      Table "public.yourtable"
 Column | Type  | Modifiers | Storage  | Stats target | Description 
--------+-------+-----------+----------+--------------+-------------
 movie  | jsonb |           | extended |              | 

ds=# INSERT INTO yourtable VALUES ('[{"title": "Pulp Fiction"}, {"tiitle": "Rambo"}]');
INSERT 0 1

ds=# SELECT * FROM yourtable yt,jsonb_array_elements(yt.movies) AS movie
WHERE movie ? 'tiitle';

SO has a lot of good resources together with Postgres documentation.