Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Hi Adrian, thank you for the reply -

On Fri, Mar 2, 2018 at 3:05 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 03/02/2018 05:52 AM, Alexander Farber wrote:

in PostgreSQL 10.3 I have the following table with a jsonb column:

#  \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                  |           |          |
Indexes:
     "words_moves_pkey" PRIMARY KEY, btree (mid)
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_daily" CONSTRAINT "words_daily_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 mid, jsonb_array_length(tiles) from words_moves where gid=609;
ERROR:  22023: cannot get array length of a scalar
LOCATION:  jsonb_array_length, jsonfuncs.c:1579

What am I doing wrong here please?

Are you sure all the values in tiles are correctly formatted because when I use jsonb_array_length with the provided data:

test=# select jsonb_array_length( '[{"col": 3, "row": 7, "value": 2, "letter": "С"}, {"col": 4, "row": 7, "value": 1, "letter": "О"}, {"col": 5, "row": 7, "value": 2, "letter": "П"}, {"col": 6, "row": 7, "value": 0, "letter": "Л"}, {"col": 7, "row": 7, "value": 3, "letter": "Я"}]');
 jsonb_array_length
--------------------
                  5

I fill that table with the following stored function (please pardon the huge listing):

CREATE OR REPLACE FUNCTION words_play_game(
                in_uid   integer,
                in_gid   integer,
                in_tiles jsonb
        ) RETURNS table (
                out_uid  integer, -- the player to be notified
                out_fcm  text,
                out_apns text,
                out_adm  text,
                out_body text
        ) AS
$func$
DECLARE
        _tile     jsonb;
        _letter   char;
        _value    integer;
        _col      integer;
        _row      integer;
        _pos      integer;
        _mid      bigint;
        _total    integer;
        _hand_len integer;
        _pile_len integer;
        _move_len integer;
        _pile     char[];
        _hand     char[];
        _letters  char[][];
        _values   integer[][];
        _opponent integer;
        _finished timestamptz;
        _reason   text;
        _score1   integer;
        _score2   integer;
BEGIN
        IF EXISTS (SELECT 1 FROM words_users
                            WHERE uid = in_uid AND
                            banned_until > CURRENT_TIMESTAMP) THEN
                RAISE EXCEPTION 'User % is banned', in_uid;
        END IF;

        -- fetch the 4 arrays (_hand, _pile, _letters, _values) for the current game
        SELECT
                hand1,
                pile,
                letters,
                values
        INTO
                _hand,
                _pile,
                _letters,
                _values
        FROM words_games WHERE
                gid     = in_gid AND
                player1 = in_uid AND
                -- game is not over yet
                finished IS NULL AND
                -- and it is first player's turn
                (played1 IS NULL OR played1 < played2);

        IF NOT FOUND THEN
                SELECT
                        hand2,
                        pile,
                        letters,
                        values
                INTO
                        _hand,
                        _pile,
                        _letters,
                        _values
                FROM words_games WHERE
                        gid     = in_gid AND
                        player2 = in_uid AND
                        -- game is not over yet
                        finished IS NULL AND
                        -- and it is second player's turn
                        (played2 IS NULL OR played2 < played1);
        END IF;

        IF NOT FOUND THEN
                RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
        END IF;

        PERFORM words_check_positions(in_uid, in_gid, in_tiles);

        FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
        LOOP
                _letter :=  _tile->>'letter';
                _value  := (_tile->>'value')::int;
                _col    := (_tile->>'col')::int + 1;
                _row    := (_tile->>'row')::int + 1;

                IF NOT words_valid_tile(_letter, _value) THEN
                        RAISE EXCEPTION 'Invalid tile = %', _tile;
                END IF;

                -- search for the played tile in the player hand
                IF _value = 0 THEN
                        _pos := ARRAY_POSITION(_hand, '*');
                ELSE
                        _pos := ARRAY_POSITION(_hand, _letter);
                END IF;

                IF _pos >= 1 THEN
                        _hand[_pos] := NULL;
                ELSE
                        RAISE EXCEPTION 'Tile % not found in hand %', _tile, _hand;
                END IF;
                
                _letters[_col][_row] := _letter;
                _values[_col][_row]  := _value;
        END LOOP;

        -- remove played tiles from player hand
        _hand     := ARRAY_REMOVE(_hand, NULL);
        -- move up to 7 missing tiles from pile to hand
        _hand_len := CARDINALITY(_hand);
        _pile_len := CARDINALITY(_pile);
        _move_len := LEAST(7 - _hand_len, _pile_len);
        _hand     := _hand || _pile[1:_move_len];
        _pile     := _pile[(1 + _move_len):_pile_len];

        INSERT INTO words_moves (
                action,
                gid,
                uid,
                played,
                tiles
        ) VALUES (
                'play',
                in_gid,
                in_uid,
                CURRENT_TIMESTAMP,
                in_tiles
        ) RETURNING mid INTO STRICT _mid;

        INSERT INTO words_scores (
                mid,
                gid,
                uid,
                word,
                score
        ) ( SELECT
                _mid,
                in_gid,
                in_uid,
                out_word,
                max(out_score)
        FROM words_check_words(in_uid, in_gid, in_tiles)
        GROUP BY out_word);

        SELECT
                SUM(score),
                words_get_given(in_uid) || ': ' || STRING_AGG(FORMAT('%s (%s)', word, score), ', ')
        INTO STRICT
                _total,
                out_body
        FROM words_scores
        WHERE mid = _mid;

        if _move_len = 7 THEN
                _total := _total + 15;
                out_body := out_body || ' +15 бонус';
        END IF;

        -- player has no tiles, game over
        IF CARDINALITY(_hand) = 0 THEN
                _finished := CURRENT_TIMESTAMP;
                _reason   := 'regular';
                -- TODO append win, loss, draw to out_body
        END IF;

        UPDATE words_moves SET
                score = _total
        WHERE mid = _mid;

        -- RAISE NOTICE '_hand     = %', _hand;
        -- RAISE NOTICE '_pile     = %', _pile;
        -- RAISE NOTICE '_letters  = %', _letters;
        -- RAISE NOTICE '_values   = %', _values;
        -- RAISE NOTICE '_hand_len = %', _hand_len;
        -- RAISE NOTICE '_pile_len = %', _pile_len;
        -- RAISE NOTICE '_move_len = %', _move_len;
        -- RAISE NOTICE '_total    = %', _total;

        -- TODO update score and store played words and stats

        UPDATE words_games SET
                finished = _finished,
                reason   = _reason,
                played1  = CURRENT_TIMESTAMP,
                score1   = score1 + _total,
                hand1    = _hand,
                pile     = _pile,
                letters  = _letters,
                values   = _values,
                state1   = words_get_state(_finished, score1 + _total, score2),
                state2   = words_get_state(_finished, score2, score1 + _total),
                hint1    = words_get_hint(_finished, FALSE, score1 + _total, score2),
                hint2    = words_get_hint(_finished, TRUE, score2, score1 + _total)
        WHERE
                gid      = in_gid AND
                player1  = in_uid AND
                -- game is not over yet
                finished IS NULL AND
                -- and it is first player's turn
                (played1 IS NULL OR played1 < played2)
        RETURNING
                player2,
                score1,
                score2
        INTO
                _opponent,
                _score1,
                _score2;

        IF NOT FOUND THEN
                UPDATE words_games SET
                        finished = _finished,
                        reason   = _reason,
                        played2  = CURRENT_TIMESTAMP,
                        score2   = score2 + _total,
                        hand2    = _hand,
                        pile     = _pile,
                        letters  = _letters,
                        values   = _values,
                        state1   = words_get_state(_finished, score1, score2 + _total),
                        state2   = words_get_state(_finished, score2 + _total, score1),
                        hint1    = words_get_hint(_finished, TRUE, score1, score2 + _total),
                        hint2    = words_get_hint(_finished, FALSE, score2 + _total, score1)
                WHERE
                        gid      = in_gid AND
                        player2  = in_uid AND
                        -- game is not over yet
                        finished IS NULL AND
                        -- and it is second player's turn
                        (played2 IS NULL OR played2 < played1)
                RETURNING
                        player1,
                        score2,
                        score1
                INTO
                        _opponent,
                        _score1,
                        _score2;
        END IF;

        IF NOT FOUND THEN
                RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
        END IF;

        -- this is the very first move in 1-player game, notification not needed
        IF _opponent IS NULL THEN
                RETURN;
        END IF;

        SELECT
                _opponent,
                fcm,
                apns,
                adm
        FROM    words_users
        WHERE   uid = _opponent
        INTO STRICT
                out_uid,
                out_fcm,
                out_apns,
                out_adm;
        -- add 1 row (containing notification) to the output table
        RETURN NEXT;
END
$func$ LANGUAGE plpgsql;