Re: In need of some JSONB examples ?

On Fri, Jan 23, 2015 at 8:00 AM, Tim Smith
<randomdev4+postgres(at)gmail(dot)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