Обсуждение: Returning values from an array of JSONB objects.

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.

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, 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

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

On 4/13/16, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
> 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}]') ;

Prerequisite:
CREATE TYPE myrowtype AS (idle bool, items int, done int);

Couple examples:

>> How can I extract the value of the 'items' key either as two rows

SELECT (jsonb_populate_recordset(NULL::myrowtype, Markers)).items
FROM Data WHERE (ID = X);

>> and or a sum of both.

SELECT sum(items) FROM(
    SELECT (jsonb_populate_recordset(NULL::myrowtype, Markers)).items
    FROM Data WHERE (ID = X)
)AS t;

> You should use a proper function "jsonb_populate_recordset" [1].
>
> [1] http://www.postgresql.org/docs/9.5/static/functions-json.html

--
Best regards,
Vitaly Burovoy

On 13/04/2016 11:53, Vitaly Burovoy wrote:
> On 4/13/16, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
>> 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}]') ;
>
> Prerequisite:
> CREATE TYPE myrowtype AS (idle bool, items int, done int);
>
> Couple examples:
>
>>> How can I extract the value of the 'items' key either as two rows
>
> SELECT (jsonb_populate_recordset(NULL::myrowtype, Markers)).items
> FROM Data WHERE (ID = X);
>
>>> and or a sum of both.
>
> SELECT sum(items) FROM(
>      SELECT (jsonb_populate_recordset(NULL::myrowtype, Markers)).items
>      FROM Data WHERE (ID = X)
> )AS t;
>
>> You should use a proper function "jsonb_populate_recordset" [1].
>>
>> [1] http://www.postgresql.org/docs/9.5/static/functions-json.html
>

Hello Vitaly,

I have just tried this and of course it works, thank you ever so much
for your time and effort I genuinely appreciate it.

--
Bill

## sighup (rts@sighup.eu):

> How can I extract the value of the 'items' key either as two rows and or
> a sum of both.

select id, jsonb_array_elements(markers)->>'items' from data;
And as jsonb_array_elements() returns a setof jsonb:
with tab(id, items) as (
  select id, (jsonb_array_elements(markers)->>'items')::integer from d
) select id, sum(items) from tab group by id;

Regards,
Christoph

--
Spare Space