Re: Calling jsonb_array_elements 4 times in the same query

Thank you - On Mon, Oct 21, 2019 at 11:20 PM Adrian Klaver wrote: > As Thomas pointed there is a difference between -> and ->>: > > test_(postgres)# select pg_typeof('[{"one": 1, "two": 2}]'::jsonb -> 0 > -> 'one'), '[{"one": 1, "two": 2}]'::jsonb -> 0 -> 'one'; > pg_typeof | ?column? > --

Re: Calling jsonb_array_elements 4 times in the same query

On 10/21/19 1:30 PM, Alexander Farber wrote: Apologies, I should have shown the JSON structure in my very first email - On Mon, Oct 21, 2019 at 4:45 PM Thomas Kellerer > wrote: Use ->> to return the value as text (not as JSONB) and you need to use the column

Re: Calling jsonb_array_elements 4 times in the same query

Apologies, I should have shown the JSON structure in my very first email - On Mon, Oct 21, 2019 at 4:45 PM Thomas Kellerer wrote: > Use ->> to return the value as text (not as JSONB) and you need to use the > column alias, not the table alias: > > (t.tile ->> 'col')::int > > It is a JSON-arr

Re: Calling jsonb_array_elements 4 times in the same query

> I am trying to create the following strored function based on your suggestion > (and I have forgotten to mention, that I also need the board id aka bid from > another table, words_games), but hit the next problem: > > CREATE OR REPLACE FUNCTION words_get_move( >     in_mid inte

Re: Calling jsonb_array_elements 4 times in the same query

On 10/21/19 6:39 AM, Alexander Farber wrote: Hello, good afternoon! With PostgreSQL 10 I host a word game, which stores player moves as a JSON array of objects with properties: col, row, value, letter - CREATE TABLE words_moves (     mid BIGSERIAL PRIMARY KEY,     action  text N

Re: Calling jsonb_array_elements 4 times in the same query

Thank you Thomas - On Mon, Oct 21, 2019 at 4:24 PM Thomas Kellerer wrote: > Alexander Farber schrieb am 21.10.2019 um 15:39: > > I am trying to construct a query, which would draw a game board when > given a move id (aka mid): > > > > SELECT > > hand, > > JSONB_ARRAY_ELEMENTS

Re: Calling jsonb_array_elements 4 times in the same query

Alexander Farber schrieb am 21.10.2019 um 15:39: > I am trying to construct a query, which would draw a game board when given a > move id (aka mid): > >     SELECT >     hand, >     JSONB_ARRAY_ELEMENTS(tiles)->'col' AS col, >     JSONB_ARRAY_ELEMENTS(tiles)->'row' AS row, >     J