Re: Returning values from an array of JSONB objects.

On 4/13/16, sighup <rts@sighup.eu> wrote:
> Hi, please excuse either my stupidity or naivety regarding this but I'm
> a bit confused. Give the following basic table structure :
>
> TABLE Data (
>     ID INT NOT NULL,
>     Markers jsonb NOT NULL
> );
>
> And the following data:
>
> INSERT INTO Data (ID, Markers) VALUES(1, '[ {"idle": true, "items": 8,
> "done": 0}, {"idle": true, "items": 8, "done": 0}]') ;
>
> How can I extract the value of the 'items' key either as two rows and or
> a sum of both.

You should use a proper function "jsonb_populate_recordset" [1].

> I had thought that :
>
> SELECT Markers->'items' AS Items FROM Data WHERE (ID = X) ;
>
> would have done the job but all I get is an empty column,

If course you get empty result because the operator "->" is "Get JSON
array *element*".

> of course using :
>
> SELECT Markers->0->'items' AS Items FROM Data WHERE (ID = X) ;
>
> Produces a single row but isn't exactly what I want.I have of course
> searched but not found a definitive answer, or maybe that should read
> 'one I can understand'.
>
> I'm using PostgreSQL 9.5 on Linux with pgAdmin III on Mac.
>
> --
> Bill

[1] http://www.postgresql.org/docs/9.5/static/functions-json.html
--
Best regards,
Vitaly Burovoy