Postgres Pro
Downloads
Home   >   mailing lists

Re: How to return a jsonb list of lists (with integers) - Mailing list pgsql-general

From Alexander Farber
Subject Re: How to return a jsonb list of lists (with integers)
Date
Msg-id CAADeyWjCunv8icoExMAZA6P9V+_aq4=2wx2ZT-hNR4kHWQofsA@mail.gmail.com
Whole thread Raw
In response to Re: How to return a jsonb list of lists (with integers)  (Alexander Farber <alexander.farber@gmail.com>)
Responses Re: How to return a jsonb list of lists (with integers)
Re: How to return a jsonb list of lists (with integers)
List pgsql-general
Tree view
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,
                        SUM(CASE WHEN (player1 = in_uid AND state1 = 'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer,
                        SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer
                )
                FROM words_games
                WHERE finished IS NOT NULL
                AND (
                        (player1 = in_uid AND player2 = in_opponent) OR
                        (player2 = in_uid AND player1 = in_opponent)
                );

                IF FOUND THEN
                        out_data := JSONB_INSERT(out_data, '{versus}', _versus);
                END IF;

pgsql-general by date:

Previous
From: Thomas Guyot
Date:
Subject: Re: PostgreSQL Replication
Next
From: Alexander Farber
Date:
Subject: Re: How to return a jsonb list of lists (with integers)