Postgres Pro
Downloads
Home   >   mailing lists

Thread: Select a column and then apply JSONB_ARRAY_ELEMENTS to it

Select a column and then apply JSONB_ARRAY_ELEMENTS to it

From
Alexander Farber
Date:
Hello,

for a word puzzle using PostgreSQL 13.1:


I am trying to improve a stored function -

CREATE OR REPLACE FUNCTION words_solve_puzzle(
                in_mid       bigint,
                in_uid       int,
                in_answer    text,
                OUT out_json jsonb
        ) RETURNS jsonb AS
$func$
DECLARE
        _tile        jsonb;
        _letter      char;
        _value       integer;
        _answer      text;
BEGIN
        FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(SELECT tiles FROM words_moves WHERE mid = in_mid)
        LOOP
                _letter :=  _tile->>'letter';
                _value  := (_tile->>'value')::int;
                RAISE NOTICE 'Tile % letter % value', _tile, _letter, _value;
        END LOOP;

However this results in the error message -

ERROR:  42601: syntax error at or near "SELECT"
LINE 24: ...  FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(SELECT til...
                                                              ^
LOCATION:  scanner_yyerror, scan.l:1180

Could you please help me, how to combine SELECT query and the LOOP through JSONB_ARRAY_ELEMENTS here?

Thank you
Alex

P.S: Here the table:

words_de=> \d words_moves
                                      Table "public.words_moves"
 Column  |           Type           | Collation | Nullable |                 Default
---------+--------------------------+-----------+----------+------------------------------------------
 mid     | bigint                   |           | not null | nextval('words_moves_mid_seq'::regclass)
 action  | text                     |           | not null |
 gid     | integer                  |           | not null |
 uid     | integer                  |           | not null |
 played  | timestamp with time zone |           | not null |
 tiles   | jsonb                    |           |          |
 score   | integer                  |           |          |
 str     | text                     |           |          |
 hand    | text                     |           |          |
 letters | character(1)[]           |           |          |
 values  | integer[]                |           |          |
Indexes:
    "words_moves_pkey" PRIMARY KEY, btree (mid)
    "words_moves_gid_played_idx" btree (gid, played DESC)
    "words_moves_uid_action_played_idx" btree (uid, action, played)
    "words_moves_uid_idx" btree (uid)
Check constraints:
    "words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
    "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
Referenced by:
    TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
    TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE


Select a column and then apply JSONB_ARRAY_ELEMENTS to it

From
"David G. Johnston"
Date:

On Saturday, January 9, 2021, Alexander Farber <alexander.farber@gmail.com> wrote:

BEGIN
        FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(SELECT tiles FROM words_moves WHERE mid = in_mid)
       

Subqueries must be enclosed in parentheses.  The parentheses that are part of the function call do not count.

David J.

Re: Select a column and then apply JSONB_ARRAY_ELEMENTS to it

From
Alexander Farber
Date:
On Sat, Jan 9, 2021 at 3:49 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
Subqueries must be enclosed in parentheses.  The parentheses that are part of the function call do not count.


Ah! Thank you David, this has worked now -

CREATE OR REPLACE FUNCTION words_solve_puzzle(
                in_mid       bigint,
                in_uid       int,
                in_guess     text,
                OUT out_json jsonb
        ) RETURNS jsonb AS
$func$
DECLARE
        _tile        jsonb;
        _letter      char;
        _value       integer;
BEGIN
        in_guess := UPPER(in_guess);
        FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS((SELECT tiles FROM words_moves WHERE mid = in_mid))
        LOOP
                _letter :=  _tile->>'letter';
                _value  := (_tile->>'value')::int;
                -- verify that all played tiles except wildcard are found in the suggested answer
                IF _value > 0 AND POSITION(_letter IN in_guess) = 0 THEN
                    out_json := json_build_object(
                            'label',    '&#x1f44e; Keep guessing!'
                    );
                    RETURN;
                END IF;
        END LOOP;

        -- check if the in_guess is one of the played words in that move
        IF NOT EXISTS(SELECT 1 FROM words_scores WHERE mid = in_mid AND word = in_guess) THEN
                out_json := json_build_object(
                        'label',    '&#x1f44e; Wrong!'
                );
                RETURN;
        END IF;

        -- the solution already submitted, just ack, but do not award coins
        IF EXISTS (SELECT 1 FROM words_puzzles WHERE mid = in_mid AND uid = in_uid) THEN
                out_json := json_build_object(
                        'label',    '&#x1f44d; Correct!',
                        'url',      '/ws/puzzle2?mid=' || in_mid || '&secret=' || MD5(in_mid || 'my secret')
                );
                RETURN;
        END IF;

        -- save the puzzle solution and award coins to the user
        INSERT INTO words_puzzles (mid, uid, solved)
        VALUES (in_mid, in_uid, CURRENT_TIMESTAMP);

        UPDATE words_users
        SET coins = coins + 1
        WHERE uid = in_uid;

        out_json := json_build_object(
                'label',    '&#x1f44d; Correct, +1 coin!',
                'url',      '/ws/puzzle2?mid=' || in_mid || '&secret=' || MD5(in_mid || 'my secret')
        );
END
$func$ LANGUAGE plpgsql;

P.S. 'my secret' is not my real secret passphrase :-)