Thread: 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