Re: How to return a jsonb list of lists (with integers)

st 17. 2. 2021 v 16:02 odesílatel Alexander Farber < alexander.far...@gmail.com> napsal: > I have tried switching to SELECT INTO, but IF FOUND is still always true, > which gives me [ null, null, null ] for some users: > > SELECT JSONB_BUILD_ARRAY( >

Re: How to return a jsonb list of lists (with integers)

st 17. 2. 2021 v 15:34 odesílatel Alexander Farber < alexander.far...@gmail.com> napsal: > Hi Pavel, > > why would SELECT INTO be better here? > Minimally it doen't use undocumented feature. And you can be sure, so the query is evaluated really like a query. The expressions are evaluated

Re: How to return a jsonb list of lists (with integers)

I have tried switching to SELECT INTO, but IF FOUND is still always true, which gives me [ null, null, null ] for some users: SELECT JSONB_BUILD_ARRAY( SUM(CASE WHEN (player1 = in_viewer AND state1 = 'won') OR (player2 = in_viewer AND state2 = 'won') THEN

Re: How to return a jsonb list of lists (with integers)

Hi Pavel, why would SELECT INTO be better here? Thanks Alex

Re: How to return a jsonb list of lists (with integers)

Hi st 17. 2. 2021 v 11:40 odesílatel Alexander Farber < alexander.far...@gmail.com> napsal: > I have tried the following, but IF FOUND is always false for some reason: > > _versus := JSONB_BUILD_ARRAY( > SUM(CASE WHEN (player1 = in_uid AND state1 = >

Re: How to return a jsonb list of lists (with integers)

I have ended up with the following (to avoid returning [null, null, null] for players who never played with each other): _versus := JSONB_BUILD_ARRAY( SUM(CASE WHEN (player1 = in_uid AND state1 = 'won') OR (player2 = in_uid AND state2 = 'won') THEN 1 ELSE

Re: How to return a jsonb list of lists (with integers)

I have tried the following, but IF FOUND is always false for some reason: _versus := JSONB_BUILD_ARRAY( SUM(CASE WHEN (player1 = in_uid AND state1 = 'won') OR (player2 = in_uid AND state2 = 'won') THEN 1 ELSE 0 END)::integer,

Re: How to return a jsonb list of lists (with integers)

Hello, thank you for the helpful replies. I have decided to go with PL/PgSQL for now and also switched from JSONB list of lists to map of lists. And the custom stored function below works mostly well, except for a special case - CREATE OR REPLACE FUNCTION words_stat_charts(

Re: How to return a jsonb list of lists (with integers)

On Tuesday, February 16, 2021, Alexander Farber wrote: > > But is it possible in SQL to combine all 3 queries, so that a JSONB list > of lists is returned? > So I have to use PL/PgSQL, correct? > With liberal usage of CTEs and subqueries writing a single SQL query should be doable. David J.

Re: How to return a jsonb list of lists (with integers)

Thank you, David, with json_build_array() it works for a single query - SELECT JSONB_BUILD_ARRAY( SUM(CASE WHEN (player1 = in_uid AND state1 = 'won') OR (player2 = in_uid AND state2 = 'won') THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN (player1

Re: How to return a jsonb list of lists (with integers)

On Tue, Feb 16, 2021 at 7:52 PM Michael Lewis wrote: > Aggregate functions work on a single column to summarize many rows into > fewer rows. You seem to be wanting to combine multiple columns which would > be done by concatenation or array[column1,column2] or something like that. > Ah right,

Re: How to return a jsonb list of lists (with integers)

Aggregate functions work on a single column to summarize many rows into fewer rows. You seem to be wanting to combine multiple columns which would be done by concatenation or array[column1,column2] or something like that.

Re: How to return a jsonb list of lists (with integers)

On Tue, Feb 16, 2021 at 11:47 AM Alexander Farber < alexander.far...@gmail.com> wrote: > Thank you for any hints > > json_build_array(...) David J.

How to return a jsonb list of lists (with integers)

Good evening, In 13.2 I have 3 SQL queries, which work well and return integer values. The values I feed to Google Charts (and currently I switch to Chart.js). Currently I use the queries by calling 3 different custom stored functions by my Java servlet. I would like to convert the functions