Re: In need of some JSONB examples ?

On Jan 23, 2015, at 7:40 AM, Tim Smith <randomdev4+postgres(at)gmail(dot)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 the format 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.