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. ;-)
On 23 January 2015 at 15:08, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Fri, Jan 23, 2015 at 8:00 AM, Tim Smith
> <randomdev4+postgres@gmail.com> wrote:
>> Hi,
>>
>> I've tried RTFMing on the wonderful new 9.4 jsonb features, but
>> there's a little bit of a lack of examples as to how to do stuff.
>>
>> I've got a document loaded in to a jsonb column that looks something like :
>>
>> [{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]
>>
>> Anyway, there are a few thousands elements in that JSON array and I've
>> tried all sorts of combinations, but I simply can't manage to figure
>> out how to :
>>
>> (a) Search by ID
>
> 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"}';
>
>> (b) Do the equivalent of select * to list all IDs and Locations (one
>> of my end goals being the ability to do a "select into" from the JSON
>> into a standard database table)
>>
>> On a completely unrelated note, I don't suppose Postgresql has any
>> built-in functionality to convert the output from an SQL query into
>> JSON ?
>
> yes. look for documentation on to_json, json_agg, json_build_object, etc.
>
> merlin
