Re: In need of some JSONB examples ?

On Jan 23, 2015, at 7:40 AM, Tim Smith <randomdev4+postgres@gmail.com> wrote:

> re: (a)
>
>> see the documentation pertaining to 'jsonb indexing', to wit:
>>
>> -- Find documents in which the key "company" has value "Magnafone"
>> SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company":
>> "Magnafone"}';
>
> Nope, sorry, tried that.  Doesn't work for me. Hence the question.  ;-)

The problem is that @> only operates at the top level of the JSON object presented to it:

xof=# TABLE j;
         f
--------------------
 [{"a": 1, "b": 2}]
 {"a": 1, "b": 2}
(2 rows)

xof=# SELECT * FROM j WHERE f @> $$ { "a": 1 } $$::jsonb;;
        f
------------------
 {"a": 1, "b": 2}
(1 row)


I'm actually not seeing a great solution to your particular problem.  If you know for sure that everything always has
theformat you describe, you can use jsonb_array_elements to extract the individual members of the array, and use @> on
them,via a JOIN, but it's not clear that an index will help you there.