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.
