Returning values from an array of JSONB objects.

Поиск
Список
Период
Сортировка
От sighup
Тема Returning values from an array of JSONB objects.
Дата
Msg-id 570E1FCA.90200@sighup.eu
обсуждение исходный текст
Ответы Re: Returning values from an array of JSONB objects.  (Vitaly Burovoy <vitaly.burovoy@gmail.com>)
Re: Returning values from an array of JSONB objects.  (Christoph Moench-Tegeder <cmt@burggraben.net>)
Список pgsql-general
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



В списке pgsql-general по дате отправления:

Предыдущее
От: Alexander Pyhalov
Дата:
Сообщение: Re: pgpool-II: cannot use serializable mode in a hot standby
Следующее
От: Vitaly Burovoy
Дата:
Сообщение: Re: Returning values from an array of JSONB objects.